I just recently spent some time with a client investigating some performance issues with mysql subqueries.
The obviously contrived example below
Depending on data size the query can run very slow. When running two queries and pasting in the actual IDs the query runs extremely fast.
The good news it is looks like it is fixed in version 6.0 - looking forward to it becoming a production release....