----- Original Message -----
From: "Jochem van Dieten" <jochemd@stripped>
To: <mysql@stripped>
Sent: Tuesday, March 21, 2006 3:53 AM
Subject: Re: RIGHT JOIN better than INNER JOIN?
> 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.
>
No, you can't. Other databases use different optimizers, i.e. different
algorithms to choose the access path. Even if you inspected 100 or 1000 or
1,000,000 Fords and found every one of them to have rear-wheel drive, it
wouldn't guarantee that BMWs or Hondas had rear-wheel drive, they might have
front-wheel drive or all-wheel drive. The specific things you learn about
MySQL's optimization technique cannot be extrapolated to other databases
unless they are using the same optimization techniques.
>
> 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
>
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006
