優化一個查詢多個表和多個條件

我需要優化具有多個連接的查詢。並且還具有多個 OR AND 條件，這些條件取決於使用者輸入。

生成的查詢是：

SELECT  emp.name as entryby, l.cname as location_name, sp.product AS product_name, 
       sp.refcode as pro_refcode, sup.vendorname, sp.mrpgross AS mrpgross,
       pro.cp AS cost_price, cust.name AS customer_names, cust.phone AS mobile_no,
       sp.uom1 as TotalQTY, sp.category_name, auth.*
FROM erp_salesorderproductsdetails sp
LEFT JOIN erp_salesorder s ON s.id = sp.salesorder_id
LEFT JOIN geopos_employees emp ON emp.id = s.entryby
LEFT JOIN geopos_locations l ON s.loc = l.id
LEFT JOIN geopos_productall pro ON pro.product_code = sp.refcode
LEFT JOIN geopos_supplier sup ON sup.id = pro.vendor
LEFT JOIN geopos_customers cust ON cust.id = s.customer
LEFT JOIN authorize_po auth ON auth.salesorder_id = s.id
WHERE sp.salesorder_id = 301 
AND ( sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )
OR sp.salesorder_id = 310 
AND ( sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )
OR sp.salesorder_id = 3234 
AND (sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )

在這裡，sp.salesorder_id根據使用者輸入，where 條件中的 AND 可以是多個。要檢索大約 700 - 800 條記錄，大約需要 2 到 3.5 分鐘。

但大多數數據計數超過 2000。在這種情況下，它的執行效率不高。任何人都可以提出任何建議嗎？

我還分享了正在生成的查詢的解釋結果。但是，共享表結構並不是一個好主意，因為有些表的列數超過 50。

1   SIMPLE  sp  ALL NULL    NULL    NULL    NULL    6211    Using where 
1   SIMPLE  s   eq_ref  PRIMARY PRIMARY 4   erp_billing.sp.salesorder_id    1       
1   SIMPLE  emp eq_ref  PRIMARY PRIMARY 4   erp_billing.s.entryby   1   Using where 
1   SIMPLE  l   eq_ref  PRIMARY PRIMARY 4   erp_billing.s.loc   1   Using where 
1   SIMPLE  pro ALL NULL    NULL    NULL    NULL    340264  Using where; Using join buffer (flat, BNL join) 
1   SIMPLE  sup eq_ref  PRIMARY PRIMARY 4   erp_billing.pro.vendor  1   Using where 
1   SIMPLE  cust    eq_ref  PRIMARY PRIMARY 4   erp_billing.s.customer  1   Using where 
1   SIMPLE  auth    ALL NULL    NULL    NULL    NULL    13  Using where; Using join buffer (flat, BNL join) 

我建議添加以下複合鍵：

ALTER TABLE erp_salesorderproductsdetails ADD INDEX `idx_1` (`salesorder_id`,`category_name`);
ALTER TABLE geopos_productall ADD INDEX `idx_2` (`product_code`,`vendor`);
ALTER TABLE erp_salesorder  ADD INDEX `idx_3` (`id`,`entryby`,`customer`);

將最後一個查詢部分從

WHERE sp.salesorder_id = 301 
AND ( sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )
OR sp.salesorder_id = 310 
AND ( sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )
OR sp.salesorder_id = 3234 
AND (sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )

至

WHERE sp.salesorder_id in (301,310,3234) 
AND  sp.category_name IN('SPECTACLES', 'SPECTACLE LENSES')

編輯：在 authorize_po 表上添加另一個鍵

ALTER TABLE authorize_po  ADD INDEX `salesorder_id` (`salesorder_id`);

引用自：https://dba.stackexchange.com/questions/315435