CODEWITHSUNDEEP
mysqlsql
CleanQuery
CleanQuery

Reputation: 25

How to optimize the query like this

My current query looks like this :

SELECT a.id,b.size,c.item_no,d.size_id 
FROM inv a LEFT JOIN product b ON a.id=b.id 
LEFT JOIN all_products c ON a.id=c.id and a.size=c.size 
LEFT JOIN 
  (SELECT qty, code, code2,status FROM prod_stock where status='1')    
  AS d ON c.web_code=d.code 
LEFT JOIN prod_size e ON a.size_id=e.prod_size_id 
WHERE a.id='123456' ORDER BY a.id,e.prod_size_id;;

As table grows( all_products has over 70,000 items), the above query takes 2~7sec. Any suggestion? My current setting is LAMP..

Upvotes: 2

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

Start by removing the subquery and the last JOIN (it does not seem necessary):

SELECT i.id, p.size, ap.item_no, ps.size_id 
FROM inv i LEFT JOIN
     product p
     ON i.id = p.id LEFT JOIN
     all_products ap
     ON i.id = ap.id and i.size = ap.size LEFT JOIN 
     prod_stock ps
     ON ap.web_code = ps.code AND status = 1 
WHERE i.id = 123456
ORDER BY i.id, i.size_id;

I removed the quotes on the constants. Presumably, they are actually numbers (if they are strings, then add the constants back in).

You want indexes on all the JOIN keys. For the first table, you specifically want inv(id, size, size_id).

Upvotes: 1

Related Questions