I just encountered this problem on MySQL. But don't blame the database, since I've seen similar misbehavior on PostgreSQL and Oracle.
Someone put together a reporting query. In it there are many left joins to subqueries. The overall query was painfully slow and getting slower over time. I was asked to improve this.
The solution is to move all of the subqueries into queries that populate temp tables. Put indexes on those temp tables. Then do the big join and watch it run much faster.
The reason why this works is that this plan is not in the query optimizer's repertoire. And the reason for that is that if the query optimizer tried to analyze every possible strategy for a complex query, it could take longer to run the analyzer than to run the query!
Still this pattern does come up from time to time, so it is a good trick to have in your toolbox.
