Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 Mar 9 '12 at 15:41
    
@JNK: Depending also on how good the optimizer is. If small and big tables are not base tables but derived ones, MySQL optimizer is not very smart to find always a good plan. –  ypercube Mar 9 '12 at 15:46
    
I think I meant is there an implementation that has the same execution plan. Might be a naive question, im pretty green when it comes to RDBMS's. I just had a problem with the first query returning different execution plans for different filter's in the where clause. –  Chris Mar 9 '12 at 15:47
3  
@Chris: "I just had a problem with the first query returning different execution plans for different filter's in the where clause. " -> this means your question is misleading. –  gbn Mar 9 '12 at 16:20
3  
Also you do realize the two queries posted are different? st.btid in the first and st.stid in the second? –  JNK Mar 9 '12 at 16:55

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.

share|improve this answer
1  
OP's comment on the Q shows that the given query isn't the actual problem. But you're correct otherwise... :-) –  gbn Mar 9 '12 at 16:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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