List:General Discussion« Previous MessageNext Message »
From:Rhino Date:March 21 2006 1:36pm
Subject:Re: RIGHT JOIN better than INNER JOIN?
View as plain text  
----- 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

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