Robert,
Your restatement of your original question uses "FULL JOIN" as if it means
the same things as "INNER JOIN": that's simply not correct. A full join
contains the results of an inner join PLUS the "orphan rows" from the
right-hand table in the join PLUS the "orphan rows" from the left-hand table
in the join. Furthermore, the last time I checked, which was probably at
least a year ago now, MySQL didn't support a full join.
--
Rhino
----- Original Message -----
From: "Robert DiFalco" <[EMAIL PROTECTED]>
To: "Martijn Tonies" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Tuesday, March 21, 2006 2:04 PM
Subject: RE: RIGHT JOIN better than INNER JOIN?
For me the argument is a little pedantic. The contract of the descriptor
table is that it must reference a name; there is code and constraints to
enforce this. I am happy to have the query return nulls to indicate a
programming error that can be quickly addressed. _If_ (after buffer
tuning et al) a RIGHT JOIN still provides a substantial performance
improvement over a FULL JOIN in this case, my customers would want me to
provide that rather than have me tell them it is an "inappropriate join"
or that I am asking the database server developers to improve their
query optimizer.
I wasn't really looking to get into a philosophical debate on
correctness so let me restate my question a little better.
Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially
out perform a FULL JOIN in those cases where the results would be
identical? It is a little difficult to test query performance
empirically since performance will change as different indices are
swapped in and out of memory buffers and such (although I have turned
query caching off), but it appears that for a table with 1-2 million
rows a query similar to what I posted here was faster with a RIGHT JOIN.
R.
-----Original Message-----
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 21, 2006 9:43 AM
To: mysql@lists.mysql.com
Subject: Re: RIGHT JOIN better than INNER JOIN?
Robert,
Of course one should use the right JOIN for the job. But let me ask
you, which join would you use here?
You have a table called Descriptors, it has a field called nameID
which is a unique key that relates to a Names table made up of a
unique identity and a VARCHAR name. I think most people would write a
simple query like this:
SELECT desc.<fields>, names.name
FROM desc JOIN names ON desc.nameId = names.Id
ORDER BY names.name
However, it this really correct? Every descriptor has a record in
names, so it could be equivalently written as:
SELECT desc.<fields>, names.name
FROM desc RIGHT JOIN names ON desc.nameId = names.Id
ORDER BY names.name
My guess is that most people conventionally write the first query.
Gee, I wonder why ... This happens to be the query that returns the rows
as it should.
What happens, if two years from now you didn't document WHY you wrote a
"right join" query instead of an inner join and someone figures out that
this could return nulls for a result column?
If you start using the wrong joins, you will make things harder on
yourself and others.
As I said: if performance isn't satisfactory (which sounds a bit strange
for this situation), then try to solve that. Either by using different
index/buffer/caching strategies or by complaining to the people who
created the database system in the first place.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
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.6/286 - Release Date: 20/03/2006
--
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]
