Mysql – What would cause a query to run slowly when used a subquery, but not when run separately


I have something similar to the following:

FROM contact AS c
WHERE IN (SELECT s.contact_id 
    FROM sub_table AS s
        LEFT JOIN contact_sub AS c2 ON ( = c2.sub_field)
    WHERE LIKE '535%')

The problem is that the query takes a very very very long time (>2minutes), but if I take the subquery, run it separately, implode the ids and insert them into the main query, it runs in well less than 1 second, including the data retrival and implosion.

I have checked the explains on both methods and keys are being used appropriately and the same ways. The subquery doesn't return more than 200 IDs.

What could be causing the subquery method to take so much longer?

BTW, I know the query above can be written with joins, but the query I have can't be–this is just a simplified version.

Using MySQL 5.0.22.

Best Solution

Related Question