为什么MySQL select count(*)在快速子查询中这么慢? [英] Why is MySQL select count(*) so slow on fast subquery?

问题描述

我有以下查询执行得非常快:

I have the following query that executes quite fast:

SELECT DISTINCT p.products_id, 
                p.products_image, 
                p.products_quantity, 
                p.products_status, 
                m.manufacturers_id, 
                p.products_date_added, 
                p.products_subimage1, 
                pd.products_name, 
                p.products_price, 
                p.products_length, 
                p.products_width, 
                p.products_height, 
                p.products_tax_class_id, 
                IF(s.status, s.specials_new_products_price, NULL)             AS 
                specials_new_products_price, 
                IF(s.status, s.specials_new_products_price, p.products_price) AS 
                final_price,
                IF(clearance_price < products_cost * 2.25, 
                clearance_price, 
                products_cost * 2.25)                                       AS 
                sorting_price 

FROM   

       (SELECT products_id ,
                IF(clearance_price < products_cost * 2.25, 
                clearance_price, 
                products_cost * 2.25)                                       AS 
                sorting_price 
                FROM `products` 
                ORDER BY products_id DESC)q,


       products p
       left join manufacturers m USING(manufacturers_id) 
       left join specials s 
              ON p.products_id = s.products_id 
       left join products_attributes pa 
              ON p.products_id = pa.products_id 
       left join products_options po 
              ON pa.options_id = po.products_options_id 
       left join products_options_values pov 
              ON pa.options_values_id = pov.products_options_values_id, 
       products_description pd, 
       categories c, 
       products_to_categories p2c 
WHERE  

       q.products_id = p.products_id 
       AND q.sorting_price = sorting_price
       AND 

       p.products_status = '1' 
       AND p.products_id = pd.products_id 
       AND pd.language_id = '1' 
       AND p.products_id = p2c.products_id 
       AND p2c.categories_id = c.categories_id 
       AND (( pd.products_name LIKE '%a%' 
               OR po.products_options_name LIKE '%a%' 
               OR pov.products_options_values_name LIKE '%a%' 
               OR pd.products_description LIKE '%a%' ))

当我将其包装在count(*)查询中时，新的count查询将花费10至15倍的时间，非常慢. 我像这样包裹它:

When I wrap it inside a count(*) query, the new count query takes 10 to 15 times more time, very slow. I wrap it like so:

SELECT count(*) as total from (
    SELECT DISTINCT p.products_id, 
                p.products_image, 
                p.products_quantity, 
                p.products_status, 
                m.manufacturers_id, 
                p.products_date_added, 
                p.products_subimage1, 
                pd.products_name, 
                p.products_price, 
                p.products_length, 
                p.products_width, 
                p.products_height, 
                p.products_tax_class_id, 
                IF(s.status, s.specials_new_products_price, NULL)             AS 
                specials_new_products_price, 
                IF(s.status, s.specials_new_products_price, p.products_price) AS 
                final_price,
                IF(clearance_price < products_cost * 2.25, 
                clearance_price, 
                products_cost * 2.25)                                       AS 
                sorting_price 

FROM   

       (SELECT products_id ,
                IF(clearance_price < products_cost * 2.25, 
                clearance_price, 
                products_cost * 2.25)                                       AS 
                sorting_price 
                FROM `products` 
                ORDER BY products_id DESC)q,


       products p
       left join manufacturers m USING(manufacturers_id) 
       left join specials s 
              ON p.products_id = s.products_id 
       left join products_attributes pa 
              ON p.products_id = pa.products_id 
       left join products_options po 
              ON pa.options_id = po.products_options_id 
       left join products_options_values pov 
              ON pa.options_values_id = pov.products_options_values_id, 
       products_description pd, 
       categories c, 
       products_to_categories p2c 
WHERE  

       q.products_id = p.products_id 
       AND q.sorting_price = sorting_price
       AND 

       p.products_status = '1' 
       AND p.products_id = pd.products_id 
       AND pd.language_id = '1' 
       AND p.products_id = p2c.products_id 
       AND p2c.categories_id = c.categories_id 
       AND (( pd.products_name LIKE '%a%' 
               OR po.products_options_name LIKE '%a%' 
               OR pov.products_options_values_name LIKE '%a%' 
               OR pd.products_description LIKE '%a%' ))
    ) AS derivedtable1

为什么会这样?有什么方法可以对此进行优化?

Why does this happen? Is there any way to optimize this?

这是第一个查询的EXPLAIN EXTENDED:

This is the EXPLAIN EXTENDED of the first query:

这是计数(第二个)查询的EXPLAIN EXTENDED:

This is the EXPLAIN EXTENDED of the count (the second) query:

这是第一个查询的配置文件:

This is the PROFILING of the first query:

这是计数(第二个)查询的概要文件:

This is the PROFILING of the count (the second) query:

推荐答案

FROM pd
LEFT JOIN po ON ...
WHERE ( ... OR po.x LIKE '...' OR ... )

LEFT的语义表示po中是否有匹配的行并不重要.因此，LIKE对查询结果的影响为零.建议您摆脱OR ... LIKE中LEFT中的所有项目.或者，也许您不是真的要离开吗?该可以使其运行更快，但结果集 可以不同.

The semantics of LEFT say that it does not matter whether there is a matching row in po. Hence the LIKE has zero impact on the outcome of the query. Suggest you get rid of OR ... LIKE of any items in LEFT. Or maybe you did not really mean LEFT? That could make it run faster, but the result set could be different.

我从EXPLAINs看到，没有时间差的好借口.哦，您如何运行查询?第一个具有21.3万行的输出-您是否在停止秒表之前等待所有输出返回?

I see from the EXPLAINs that there is no good excuse for the difference in timing. Oh, how are you running the queries? The first one has 213K rows of output -- are you waiting for all of it to come back before stopping your stopwatch?

这篇关于为什么MySQL select count(*)在快速子查询中这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！