The first line of the MySQL 5.1 documentation for subquery optimization:
Development is ongoing, so no optimization tip is reliable for the long term.
They're not kidding, either. Subquery optimization in MySQL is terribly broken, as we've discovered today. We have Matt Trout in for some consulting and even ridiculously stupid subqueries have terrible query plans, forcing us to to consider nasty workarounds, one of which is called "PostgreSQL".