Please help me understand why a sub-query affects the main query's use of index

Here is the main query without a sub-query:

SELECT * FROM 
    mytable AS idx
WHERE 
    idx.ID IN (1,2,3) 
    AND idx.P1 = 'galleries';

The index on this table is id_path (ID,P1)

Everything is fine at this point, the index is used, 3 rows are examined and 2 开发者_开发问答are returned. Without the index 9 rows would have to be examined.

Now if i replace the list of IDs with a sub-query that returns exactly the same set of IDs, the main query still returns the correct rows, but it stops using the index and does an examination of 9 rows as if the index never even existed.

SELECT * FROM 
    mytable AS idx
WHERE 
    idx.ID IN (SELECT idxrev.ID FROM mytable AS idxrev WHERE idxrev.ID IN (1,2,3)) 
    AND idx.P1 = 'galleries';

My question is, why does this happen and what could i do to make the main query use the index as before. I tried adding USE INDEX (id_path) but that just made it even worse, doing a whole table scan.

SELECT  *
FROM    mytable AS idx
WHERE   idx.ID IN
        (
        SELECT  idxrev.ID
        FROM    mytable AS idxrev
        WHERE   idxrev.ID IN (1,2,3)
        ) 
        AND idx.P1 = 'galleries'

MySQL's only way to make semi-joins is nested loops.

It needs to take every row of idx and check it against idxrev (using the indexes for that).

Of course a better method in this case would be a HASH SEMI JOIN or just reducing your query to the original one, but MySQL is just not capable of it.

To make the query use the index, just revert to your original query :)

That's one of the great mysteries of MySQL; it doesn't cope well with subqueries. You could try to change the IN to an EXISTS which is sometimes faster. It looks a bit silly in this example because you still use the hardcoded list, but I think thats just for testing, right?

SELECT * FROM 
    mytable AS idx
WHERE 
    idx.ID EXISTS 
        (SELECT idxrev.ID 
        FROM mytable AS idxrev 
        WHERE 
           idxrev.ID = idx.ID AND
           idxrev.ID IN (1,2,3)) 
    AND idx.P1 = 'galleries';

If this doesn't help, maybe you could run two queries. First you get all the ids an put them in a comma separated list (using GROUP_CONCAT if you like). Then you build the second query by using that value.