So I assumed databases would have a pretty good SQL optimisation story by now, but no… Two recent disappointments:
- MySQL failed to notice that
SELECT ... FROM ... WHERE ? IN (foo, bar)was equivalent to... WHERE foo = ? OR bar = 2and/or that the latter would be much faster. - 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?)