mysql - 为什么 MySQL select count(*) 在快速子查询上这么慢？

Question

我有以下执行速度非常快的查询:

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 倍的时间，非常慢。我这样包装它:

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

为什么会这样？有什么办法可以优化吗？

编辑:这是第一个查询的 EXPLAIN EXTENDED:

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

这是第一个查询的 PROFILING:

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

Answer 1

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

LEFT 的语义表示 po 中是否有匹配的行并不重要。因此，LIKE 对查询结果的影响为零。建议您去掉 OR ... LIKE 中 LEFT 中的任何项目。或者，也许您并不是真正的意思是 LEFT？这可能使其运行得更快，但结果集可能不同。

我从 EXPLAINs 中看出，时间上的差异没有好的借口。哦，你是如何运行查询的？第一个有 213K 行的输出——你是不是在等所有的输出都回来再停止你的秒表？