I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes:

select * from smalltable st
    inner join bigtable bt on st.btid = bt.btid

select * from bigtable bt
    inner join smalltable st on bt.btid = st.btid

As well as the individual queries finding different execution plans for different strings in the where filters.

Are there any optimizing, fully declarative implementations of the SQL standard, such that the two above queries would have equal execution plans?

  • 6
    They ARE equal, but the way that the optimizer interprets them could be different depending on statistics and whatnot. I'm sure your final result set was equal, but you may have had a suboptimal execution plan depending on how your stats are.
    – JNK
    Commented Mar 9, 2012 at 15:41
  • 3
    Also you do realize the two queries posted are different? st.btid in the first and st.stid in the second?
    – JNK
    Commented Mar 9, 2012 at 16:55
  • In MySQL, you can enforce the order that the join occurs with STRAIGHT_JOIN. Commented Mar 11, 2012 at 2:02

1 Answer 1


Any database with a cost-based optimizer (which includes basically any enterprise-class database) would generally treat those two queries as identical. But no cost-based optimizer is or can be perfect-- they will all occasionally pick a poor plan.

If you're using a cost-based optimizer and the optimizer has reasonably accurate information about the tables and indexes (this information is generally gathered asynchronously and generally gathered in the evening/ morning when other activity is relatively light), it should be relatively clear (for some values of smalltable and bigtable and assuming proper indexes) that the most efficient plan is to do a full table scan of smalltable and then do index lookups on bigtable for those rows using the join condition. If the database thinks that smalltable has 10% of the rows in bigtable rather than 0.1% of the rows in bigtable, on the other hand, there are a lot of different plans that have to be considered. As the number of tables, the number of predicates, and the number of access paths the database supports grows, the potential number of query plans grows exponentially. But the time the optimizer has to find the best plan does not grow-- you're generally going to be upset if the database takes a couple minutes to figure out the best plan before it even starts to execute the query. So cost-based optimizers will never be perfect-- they don't have the time to fully explore every path and they rely on statistics that may be stale in order to figure out which plans are optimal. Most of the time, however, they're quite good at figuring out the best way to join two tables when a query has no predicates and a simple join condition.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.