MySQL Forums
Forum List  »  Newbie

Re: Fast 3 Table Join
Posted by: Rick James
Date: March 24, 2012 09:49PM

> "WHERE A.state = "state"".
That is a totally different situation. So, let's start over. You want to optimize this, correct? Completely correct? No GROUP BY, ORDER BY, or LIMIT, correct? Do you really use "*"; if you don't, that could impact the optimization.

SELECT  *
    FROM  A,B,C
    WHERE  A.idA = B.idA
      AND  C.idC = B.idC
      AND  A.state = 'abc';

Stick EXPLAIN in front of the SELECT and provide us with the output. I suspect it will list the tables in order: A, B, C.

A needs an index beginning with `state`.
B needs an index beginning with idA, preferably a compound index (idA, idC).
C needs an index beginning with idC.

Options: ReplyQuote


Subject
Written By
Posted
March 21, 2012 07:57AM
March 21, 2012 08:07AM
March 21, 2012 08:08AM
March 21, 2012 08:16AM
March 22, 2012 10:01PM
March 23, 2012 06:04AM
Re: Fast 3 Table Join
March 24, 2012 09:49PM
March 24, 2012 10:04PM
March 25, 2012 05:59AM
March 26, 2012 10:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.