MySQL not using indexes

Question

Hi, I just enabled the slow-log (+not using indexes) and I'm getting hundreds of entries for the same kind of query (only user changes)

SELECT id
     , name 
  FROM `all` 
 WHERE id NOT IN(SELECT id 
                   FROM `picks` 
                  WHERE user=999) 
ORDER BY name ASC;

EXPLAIN gives:

+----+--------------------+-------------------+-------+------------------+--------+---------+------------+------+------------------------------------------+
| id | select_type        | table             | type  | possible_keys    | key    | key_len | ref        | rows | Extra                                    |
+----+--------------------+-------------------+-------+------------------+--------+---------+------------+------+------------------------------------------+
|  1 | PRIMARY            | all               | index | NULL             | name   | 156     | NULL       |  209 | Using where; Using index; Using filesort | 
|  2 | DEPENDENT SUBQUERY | picks             | ref   | user,user_2,pick | user_2 | 8       | const,func |    1 | Using where; Using index                 | 
+----+--------------------+-------------------+-------+------------------+--------+---------+------------+------+------------------------------------------+

Any idea about how to optimize this query? I've tried with a bunch of different indexes on different fields but nothing.

Answer 1

"not in" and "exists" always bad choices for performance. May be left join with cheking "NULL" will be better try it.

Answer 2

I don't necessarily agree that 'not in' and 'exists' are ALWAYS bad performance choices, however, it could be in this situation.

You might be able to get your results using a much simpler query:

SELECT id
     , name 
  FROM `all`
     , 'picks'
 WHERE all.id = picks.id
   AND picks.user <> 999 
ORDER BY name ASC;

Answer 3

This is probably the best way to write the query. Select everything from all and try to find matching rows from picks that share the same id and user is 999. If such a row doesn't exist, picks.id will be NULL, because it's using a left outer join. Then you can filter the results to return only those rows.

SELECT all.id, all.name 
FROM
    all
    LEFT JOIN picks ON picks.id=all.id AND picks.user=999
WHERE picks.id IS NULL
ORDER BY all.name ASC