List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:March 21 2006 8:53am
Subject:Re: RIGHT JOIN better than INNER JOIN?
View as plain text  
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
Thread
RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Jochem van Dieten21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Martijn Tonies21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar
  • RE: RIGHT JOIN better than INNER JOIN?Nicolas Verhaeghe21 Mar
RE: RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Martijn Tonies21 Mar
RE: RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?gerald_clark21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar