MySQL Forums :: Optimizer & Parser :: Optimizing query

Advanced Search

Optimizing query
Posted by: jr wakabayashi ()
Date: May 23, 2011 04:12PM

I have a query which has multiple joins and i'm trying to find a way to optimize it. What is the best solution? Is there a software that can look at the query and make suggestions on what to do, what indexes need to be created? My query is below...

I have an index on the models (modelid) and manufacturers (mfrid) at the moment. But even then it takes about 30 seconds to complete.

Thanks for any help.

SELECT DISTINCT models.modelid, models.ModelName FROM class INNER J
OIN (manufacturers RIGHT JOIN (((crossref INNER JOIN (costco_skus RIGHT JOIN pat
riotpart_tbl ON costco_skus.Costco_SKUS = patriotpart_tbl.partnumber) ON crossre
f.CrossrefId = patriotpart_tbl.crossrefid) INNER JOIN OEMMemory ON crossref.Cros
srefId = oemmemory.CrossRefID) INNER JOIN models ON OEMMemory.ModelID = models.m
odelid) ON manufacturers.mfrid = models.mfrid) ON class.catid = models.catid WHE
RE (class.catid = ? AND manufacturers.mfrid = ? AND ((costco_skus.crossref) In (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))) ORDER BY models.modelname ASC

Options: ReplyQuote

Subject Views Written By Posted
Optimizing query 2386 jr wakabayashi 05/23/2011 04:12PM
Re: Optimizing query 807 Jørgen Løland 05/24/2011 12:49AM
Re: Optimizing query 796 Rick James 05/27/2011 08:55AM
Re: Optimizing query 800 Jørgen Løland 05/28/2011 12:30AM
Re: Optimizing query 868 Rick James 05/28/2011 10:40PM
Re: Optimizing query 706 Jørgen Løland 05/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.