Archive for July 31st, 2009
From Stack Overflow:
I currently have an
itemtable, and a
pairtable simply contains two columns, which contain the
PRIMARY KEYfrom the
itemtable. There is an index on
A common query is to find a number of items that are featured in the least number of pairs:SELECT id, COUNT(*) AS count FROM item i LEFT JOIN pair p ON (p.id1 = i.id OR p.id2 = i.id) GROUP BY id ORDER BY count, RAND() LIMIT 100
, but the query is horible performance wise.
Is there a better query, and/or data structure for this type of thing?
MySQL is not very good in optimizing
It’s capable of doing
index_merge, which would
UNION the results of two indexes, however, as documentation states, this access method works only when comparing the fields against the constants.
This method, therefore, can be used to serve an
OR condition neither in a
JOIN nor in a subquery.
Let’s create sample tables and see the execution plan for the query: