----- Original Message -----
From: "Jochem van Dieten" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
