Robert DiFalco wrote:
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.
You have not given enough information to even make a guess.
Show the create tables for each table, and the output of
explain for each query to see what keys are being used.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
