需要帮助来优化MySQL查询,只需很少的连接

时间:2021-11-11 02:22:17

Hello I have the follow sql query it works but it is kind of slow

您好我有以下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

The Product Table has total 462 rows,

产品表共有462行,

The Order Statuses Tables has total 17 154 rows

订单状态表共有17 154行

The execution time of the query is 1.5 secs which I think it is slow.

查询的执行时间是1.5秒,我认为它很慢。

I use the INNER SELECT in the ON clause to get the last status for the given order, and here I think there is something wrong.

我在ON子句中使用INNER SELECT来获取给定订单的最后状态,在这里我认为有些错误。

WHen I remove the order statuses table from the whole query the execution time is several times faster 0.152 secs

当我从整个查询中删除订单状态表时,执行时间比0.152秒快几倍

Could you give me some advice how can I optimize the query I have

你能给我一些建议我如何优化我的查询

Here you go the sql with explain

在这里你去解释sql

需要帮助来优化MySQL查询,只需很少的连接

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

1 个解决方案

#1


0  

In your case it is easier to perform some operations not using mysql but using php. I have seen in my practice cases when you win time. Please, try to divide a complex query into several simple queries. Then perform the necessary filtering using php scripts. Hope it helps. Thanks!

在你的情况下,更容易执行一些不使用mysql但使用php的操作。我在练习中看到了你赢得时间的情况。请尝试将复杂查询划分为几个简单查询。然后使用PHP脚本执行必要的过滤。希望能帮助到你。谢谢!

#1


0  

In your case it is easier to perform some operations not using mysql but using php. I have seen in my practice cases when you win time. Please, try to divide a complex query into several simple queries. Then perform the necessary filtering using php scripts. Hope it helps. Thanks!

在你的情况下,更容易执行一些不使用mysql但使用php的操作。我在练习中看到了你赢得时间的情况。请尝试将复杂查询划分为几个简单查询。然后使用PHP脚本执行必要的过滤。希望能帮助到你。谢谢!