Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

btilly (5037)

btilly
  (email not shown publicly)

Journal of btilly (5037)

Sunday March 01, 2009
11:30 PM

Lots of left joins on subqueries can be slow

[ #38573 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.