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).

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.