需要帮助优化有少量连接的MySQL查询[英] Need Help for Optimizing MySQL query with few joins

问题描述

你好，我有关注SQL查询，它有效，但有点慢

SELECT 
    ep . *,
    t_u.sUsername AS sLockedBy,
    epi.sName,
    em.sName AS sManufacturerName,
    SUM(IF(eop.dInProduction IS NULL
            AND eop.dFromProduction IS NULL
            AND eop.dShipped IS NULL
            AND eop.nIsCancelled = 0
            AND eos.sStatusCode NOT IN ('Canceled' , 'Finished', 'Complete'),
        1,
        0)) AS nProductOrdered,
    SUM(IF(eop.dInProduction IS NOT NULL
            AND eop.dFromProduction IS NULL
            AND eop.dShipped IS NULL
            AND eop.nIsCancelled = 0
            AND eos.sStatusCode NOT IN ('Canceled' , 'Finished', 'Complete'),
        1,
        0)) AS nProductInProduction,
    SUM(IF(eop.dInProduction IS NOT NULL
            AND eop.dFromProduction IS NOT NULL
            AND eop.dShipped IS NULL
            AND eop.nIsCancelled = 0
            AND eos.sStatusCode NOT IN ('Canceled' , 'Finished', 'Complete'),
        1,
        0)) AS nProductFromProduction,
    SUM(IF(eop.dInProduction IS NULL
            AND eop.dFromProduction IS NULL
            AND eop.dShipped IS NULL
            AND eop.nIsCancelled = 0
            AND eos.sStatusCode NOT IN ('Canceled' , 'Finished', 'Complete'),
        1,
        0)) + ep.nInStock AS nStockCheck
FROM
    eshop3_products AS ep
        LEFT JOIN
    users AS t_u ON ep.nUserLockID = t_u.nUID
        AND t_u.nDeleted = 0
        INNER JOIN
    eshop3_products_i18n AS epi ON ep.nUID = epi.nProductID
        AND epi.nLangID = 1
        AND epi.nDeleted = 0
        LEFT JOIN
    eshop3_manufacturers AS em ON ep.nManufacturerID = em.nUID
        AND em.nDeleted = 0
        LEFT JOIN
    eshop3_order_products AS eop ON ep.nUID = eop.nProductID
        LEFT JOIN
    eshop3_orders AS eo ON eop.nOrderID = eo.nUID
        LEFT JOIN
    eshop3_order_statuses AS eos ON eo.nUID = eos.nOrderID
        AND eos.nUID = (SELECT 
            nUID
        FROM
            eshop3_order_statuses as eos
        WHERE
            eo.nUID = eos.nOrderID
        ORDER BY eos.nUID DESC
        LIMIT 1)
WHERE
    (ep.nDeleted = 0)
GROUP BY ep.nUID
ORDER BY ep.sStoreCode ASC

产品表有462行，

订单状态表总计17 154行

查询的执行时间为1.5秒，我认为它很慢.

我在ON子句中使用内部选择来获得给定订单的最后一个状态，在这里我认为有问题.

当我从整个查询中删除订单状态表时，执行时间更快0.152秒

您能给我一些建议，我如何优化我拥有的查询

在这里，您可以使用SQL进行解释

id  select_type        table type   possible_keys    key      key_len ref                            rows  Extra
1   PRIMARY            epi   ALL    nProductID                                                        408  Using where; Using temporary; Using filesort
1   PRIMARY            ep    eq_ref PRIMARY,nUID     PRIMARY        4 dragoni_zfms.epi.nProductID       1  Using where
1   PRIMARY            em    eq_ref PRIMARY          PRIMARY        4 dragoni_zfms.ep.nManufacturerID   1 
1   PRIMARY            t_u   eq_ref PRIMARY          PRIMARY        4 dragoni_zfms.ep.nUserLockID       1 
1   PRIMARY            eop   ref    id_productid     id_productid   5 dragoni_zfms.ep.nUID             13 
1   PRIMARY            eo    eq_ref PRIMARY          PRIMARY        4 dragoni_zfms.eop.nOrderID         1  Using index
1   PRIMARY            eos   eq_ref PRIMARY,nOrderID PRIMARY        4 func                              1  
2   DEPENDENT SUBQUERY eos   ref    nOrderID         nOrderID       4 dragoni_zfms.eo.nUID              1  Using where; Using index; Using filesort

推荐答案

在您的情况下，执行一些不使用MySQL而是使用PHP的操作更容易. 当您赢得时间时，我已经在练习案例中看到了.请尝试将复杂的查询分为几个简单的查询.然后使用PHP脚本执行必要的过滤. 希望能帮助到你. 谢谢！

本文地址：https://www.itbaoku.cn/post/1793980.html