List:General Discussion« Previous MessageNext Message »
From:gerald_clark Date:March 21 2006 7:15pm
Subject:Re: RIGHT JOIN better than INNER JOIN?
View as plain text  
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.
Thread
RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Jochem van Dieten21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Martijn Tonies21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar
  • RE: RIGHT JOIN better than INNER JOIN?Nicolas Verhaeghe21 Mar
RE: RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Martijn Tonies21 Mar
RE: RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?gerald_clark21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar