主页 / dba / 问题 / 315435
Accepted
Parikshit Bharadwaj
Asked: 2022-08-10 11:14:46 +0800 CST

优化一个查询多个表和多个条件

  • 772

我需要优化具有多个连接的查询。并且还具有多个 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)
mysql php

1 个回答

  1. Best Answer

    我建议添加以下复合键：

    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`);
    • 3

相关问题