HAYCORN — 19 March 2012

What SQL Query "Optimisation" Has Done For Me Lately

So I assumed databases would have a pretty good SQL optimisation story by now, but no… Two recent disappointments:

  1. MySQL failed to notice that SELECT … FROM … WHERE ? IN (foo, bar) was equivalent to … WHERE foo = ? OR bar = 2 and/or that the latter would be much faster.
  2. Copying a database from one server to another led to a query that took 20 seconds on the source timing out (after 10 minutes) on the copy. Even more bizarrely, the fix was to tell the copy to ignore an index. (The master and copy weren’t exactly the same version of MySQL, but they were both varieties of 5.1.x.)

The first time it sees a query, MongoDB runs multiple query plans in parallel, remembers which one worked best, and uses that the next time. Is there any relational database that does this? Seems like a good idea, given recent experiences… Are there any web pages showing what clever things query optimisers get up to, especially optimisations that aren’t immediately obvious to a human being? (For a gcc example, see Will it Optimize?)