First off, don’t get me wrong - I love me some MySQL, but because I’ve spent way more time than I ever should have in understanding this problem I’m going to partake in some knowledge sharing. The first three links below are mysql bug entries and the last one is from Xaprb (major props).

• Optimization Problem with a subquery in an IN Operation
• Extremely Slow “Where in (Select… )” Subqueries GROUP BY / HAVING related
• Extremely Slow “Where in (Select… )” Subqueries GROUP BY / HAVING related”
• How to optimize subqueries and joins in MySQL (!)

Here’s the money shot from that last link above:

For each row in the outer query, it’s performing the inner query, even though there are no references in the inner query to values in the enclosing scope, because it has “optimized” the inner query by rewriting it to refer to the outer query. At this point, the query plan becomes nested loops. For each loop in the outer query, the query probes into the inner query.

Wow. That’s special. Translation. Don’t use subqueries in MySQL (at least not in 5.0.x). WTF.