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.
