What SQL Query "Optimisation" Has Done For Me Lately

19 March 2012

So I assumed data­bases would have a pretty good SQL op­ti­mi­sa­tion story by now, but no… Two recent disappointments:

  1. MySQL failed to notice that SELECT … FROM … WHERE ? IN (foo, bar) was equiv­a­lent to … WHERE foo = ? OR bar = 2 and/or that the latter would be much faster.
  2. Copying a data­base 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 va­ri­eties of 5.1.x.)

The first time it sees a query, MongoDB runs mul­ti­ple query plans in parallel, re­mem­bers which one worked best, and uses that the next time. Is there any re­la­tional data­base that does this? Seems like a good idea, given recent experiences… Are there any web pages showing what clever things query op­ti­mis­ers get up to, es­pe­cially op­ti­mi­sa­tions that aren’t im­me­di­ately obvious to a human being? (For a gcc example, see Will it Optimize?)