On 3/21/06, Robert DiFalco wrote: > I apologize if this is a naive question but it appears through my > testing that a RIGHT JOIN may out perform an INNER JOIN in those cases > where they would produce identical result sets. i.e. there are no keys > in the left table that do not exist in the right table. > > Is this true?
If your benchmark shows it it must be true :) But is it a significant difference? Over how many test runs? And is it worth the risk that for some join it is actually much slower. > If so, it this peculiar to MySQL or would this be true > with almost all database servers? Hard to tell. If you want a generic answer the way to go wouls be to start digging in the MySQL sourcecode to find out why it is faster. Once you know that, you can extrapolate to other databases. The only mechanism I can imagine is that you are constraining the planner so for a join between N tables you go from N! plans to (N-1)! plans which will save you a miniscule amount on the planning time for a small join, and somwhat more on a big join. (Does the MySQL planner do an exhaustive search?) The downside is that the plan might be significantly worse so you can loose big time in the executor. Especially considering the semantic difference between both syntaxes I would never use this as a way to constrain the planner. Jochem
