MySQL Limitations Part 3: Subqueries
The following query will surprise users unpleasantly:
select * from a where a.id in (select id from b);
Users expect the inner query to execute first, then the results to be substituted into the IN() list. But what happens instead is usually a full scan or index scan of table a, followed by N queries to table b. This is because MySQL rewrites the query to make the inner query dependent on the outer query, which could be an optimization in some cases, but de-optimizes the query in many other cases.