Search code examples
mysqlin-subquery

MySQL disregards indexes when using IN(SUBQUERY)?

I have the following query.

SELECT MIN(col1) FROM table1 WHERE id1 IN (SELECT id2 FROM table2)

This produces output that takes ~5 seconds.

However, if I change the query to run as two separate queries, such that:

SELECT id2 FROM table2
SELECT MIN(col1) FROM table1 WHERE id1 IN (_results_from_first_query_)

Then this produces output that takes ~0.05 seconds.

An EXPLAIN shows the following (Apologies for the terrible indenting):

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     table1 ALL  NULL    NULL    NULL    NULL    1107294     Using where
2   DEPENDENT SUBQUERY  table2 eq_ref   PRIMARY     PRIMARY     16  const,func  1   Using index

vs

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  table1  range   PRIMARY,id1     id1     8   NULL    12068   Using where

Why is my query that uses a sub query not using the index?

Further to this, IF the size of my set of IDs from table2 exceeds 650 the index will also be dropped for some reason when doing the IN(_results_from_first_query_) example. Why is this?

Solution

  • How about ::

    Create an index on table1 for id1 and table2 for id2 and the query should be::

    SELECT MIN(col1) FROM table1 

inner join table2 on id1=id2