You’d think in 2013, query optimization would not be something we have to spend much time worrying about. But sadly, artificial intelligence has not come up with a solution to save us from this boring discipline.
Take for example this query:
Pretty simple right? The query pulls back the last 6 orders with the number of items purchased for that order.
This query takes 5+ seconds to run, and touches 42,000+ rows on one install I tested. Oof.
Meanwhile, checkout this query:
This query takes 0.02 seconds to run. Same record set.
Why? The first query takes all orders and joins all ordered items, for a massive result set of 42,000 rows. It then works backwards to filter out all but 6 records.
The second query first pulls back 6 orders, and then joins the items table. A very simple operation.
Again, I don’t understand why SQL engines can’t intelligently run both queries the same way, but I’m sure there is a NP-hard reason behind it.