The only relational databases I've ever used to any significant extent are
MySQL and DB2. I've used DB2 for a lot longer than MySQL and on most of the
platforms on which it runs over various versions. As far as I'm concerned,
the answer to your questions, at least as far as DB2 goes, is: it depends.
It depends on a host of factors. In no particular order, these factors
include:
- which version of DB2 you are using
- what hardware you are running on
- how you write your SQL
- whether the data is properly clustered
- whether the tables and indexes have been reorganized in a timely fashion
- etc. etc.
You simply can't make a categorical statement that a right join will perform
better than an inner join - or vice versa - in every case in DB2. All
versions of DB2 use a cost-based optimizer that makes great efforts to give
the optimum access path (and therefore optimum performance) for each query.
A lot of very smart people have worked on the design of that optimizer over
the years - I've met some of them - but, as good as the DB2 optimizer is, it
can still make inappropriate decisions. This happens when you don't do
routine maintenance like reorganizing tables and the RUNSTATS utility but
the way you write (or mis-write) your SQL can also affect your access path
and therefore your performance.
This unpredictability may sound like a bad thing but it is often a very good
thing since the optimizer has many "tricks" and shortcuts. It will often
rewrite a poorly-written query to improve its performance.
You may be able to find more categorical answers for the other major
databases, like Oracle, since they tend to use different optimizer designs.
The only way to be really sure though is to do a proper benchmark for all
the platforms and configurations that interest you.
--
Rhino
----- Original Message -----
From: "Robert DiFalco" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Monday, March 20, 2006 7:11 PM
Subject: RIGHT JOIN better than INNER JOIN?
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 so, it this peculiar to MySQL or would this be true
with almost all database servers? i.e. Oracle, DB2, MSSQL, etc.
TIA,
R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
