Stories
Slash Boxes
Comments

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Monday February 25, 2008
01:18 PM

MySQL Pain of the Day

[ #35752 ]

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".


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

MySQL Pain of the Day 2 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • But still not perfect. In the cases where it doesn't do what I want I've found temp tables to be invaluable in getting the query plan that I want. I have even pulled such tricks as creating temp tables, creating indexes on those temp tables, then doing my join.

    In any case the bigger underlying problem with MySQL is a fundamental design decision. The problem is that the time to compile a query is part of its run time. MySQL went the route of trying to make compilation fast. That means that it does a bad
  • The MySQL docs state pretty plainly that subqueries have not been optimized at all and won't be until the 6.0 release. In the meantime, you have to pay attention to how you use them. The xaprb.com stuff is a good resource.

    In most cases, it works out best to use derived tables (subqueries in the FROM clause), which are implemented with a temporary table and force the evaluation of the subquery before the JOIN order is determined. The only situation where this can be a negative is when you use LIMIT, since
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.