I have something similar to the following:
SELECT c.id FROM contact AS c WHERE c.id IN (SELECT s.contact_id FROM sub_table AS s LEFT JOIN contact_sub AS c2 ON (s.id = c2.sub_field) WHERE c2.phone LIKE '535%') ORDER BY c.name
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.
Sounds suspiciously like MySQL bug #32665: Query with dependent subquery is too slow.