MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing query
Posted by: Rick James
Date: May 27, 2011 08:55AM

SELECT  DISTINCT models.modelid, models.ModelName
    FROM  class
    INNER JOIN (manufacturers
        RIGHT JOIN  (((crossref
                INNER JOIN  (costco_skus
                     RIGHT JOIN  patriotpart_tbl
                           ON costco_skus.Costco_SKUS = patriotpart_tbl.partnumber)
                      ON crossre f.CrossrefId = patriotpart_tbl.crossrefid)
                            INNER JOIN  OEMMemory
                                  ON crossref.CrossrefId = oemmemory.CrossRefID)
                    INNER JOIN  models
                          ON OEMMemory.ModelID = models.modelid)
                 ON manufacturers.mfrid = models.mfrid)
             ON class.catid = models.catid
            WHERE (class.catid = ?
              AND  manufacturers.mfrid = ?
              AND  ((costco_skus.crossref) In ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ))
    ORDER BY  models.modelname ASC
My head spins!

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 ]
How much RAM do you have?

Jørgen, I have perceived that the optimizer will turn LEFT JOIN into plain JOIN if there is nothing requiring LEFTness. If I recall correctly, I have seen this in EXPLAIN EXTENDED where the "LEFT" has been dropped. Am I wrong?

Options: ReplyQuote


Subject
Views
Written By
Posted
3461
May 23, 2011 04:12PM
1103
May 24, 2011 12:49AM
Re: Optimizing query
1091
May 27, 2011 08:55AM
1091
May 28, 2011 12:30AM
1106
May 28, 2011 10:40PM
1057
May 29, 2011 12: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.