MySQL Forums
Forum List  »  Newbie

Re: Optimization for a particular query
Posted by: Rick James
Date: December 19, 2009 10:03AM

Some comments inline:
SELECT  C.tN, B.loID, A.lcID
    FROM  A
    INNER JOIN  B ON B.oID = A.oID
    INNER JOIN  C ON A.cID = C.cID
    LEFT JOIN    -- Why "LEFT"?
        ( SELECT  cID, SUM(count) as mCnt
            from  C
            INNER JOIN  ME on C.tID = ME.tID
            GROUP BY  C.cID
        ) AS MT  ON MT.cID = A.cID
    WHERE  (loID = 'XYZ' )  -- which table has loID??
      AND  (MT.mCnt >= 1000)  -- Move this into MT as HAVING?
    )  -- unbalanced?
UNION  ALL   -- time each SELECT so we can see which is slower
(
SELECT  C.tN, B.loID, A.lcID
    FROM  A
    INNER JOIN  
        ( SELECT  DISTINCT M.mlcID
            FROM  A
            INNER JOIN  B ON B.oID = A.oID
            INNER JOIN  C ON A.cID = C.cID
            LEFT JOIN   -- Why "LEFT"?
                ( SELECT  cID, SUM(count) as mCnt
                    from  C
                    INNER JOIN  ME on C.tID = ME.tID
                    GROUP BY  C.cID
                ) AS MT ON MT.cID = A.cID
            INNER JOIN  M ON A.lcID = M.lcID
            WHERE  (loID = 'XYZ' )
              AND  (MT.mCnt >= 1000)
              AND  (M.mType = 'lmn' )
        ) AS MT ON A.lcID = MT.mlcID
    INNER JOIN  B ON B.oID = A.oID
    INNER JOIN  C ON A.cID = C.cID
    LEFT JOIN  
        ( SELECT  cID, SUM(count) as mCnt
            from  C
            INNER JOIN  ME on C.tID = ME.tID
            GROUP BY  C.cID
        ) AS MT ON MT.cID = A.cID
    WHERE  (MT.mCnt >= 1000)
)
LIMIT  0, 500

Is one of the subqueries the same in both parts? If so, pull it out into a TEMPORARY TABLE -- that would avoid evaluating it twice.

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Options: ReplyQuote


Subject
Written By
Posted
Re: Optimization for a particular query
December 19, 2009 10:03AM


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.