Mysql慢查询优化

Question

我有一个购物车，它不断记录像这样的缓慢查询...

# Query_time: 4  Lock_time: 0  Rows_sent: 50  Rows_examined: 454403
    SELECT SQL_CALC_FOUND_ROWS products.*, 
                           descr1.product                          AS product, 
                           Min(prices.price)                       AS price, 
                           GROUP_CONCAT(IF(products_categories.link_type = 'M', 
                           Concat(products_categories.category_id, 
                           'M'), products_categories.category_id)) AS 
                           category_ids, 
                           cscart_seo_names.name                   AS seo_name 
FROM   cscart_products AS products 
       LEFT JOIN cscart_product_descriptions AS descr1 
         ON descr1.product_id = products.product_id 
            AND descr1.lang_code = 'EN' 
       LEFT JOIN cscart_product_prices AS prices 
         ON prices.product_id = products.product_id 
            AND prices.lower_limit = 1 
       INNER JOIN cscart_products_categories AS products_categories 
         ON products_categories.product_id = products.product_id 
       INNER JOIN cscart_categories 
         ON cscart_categories.category_id = products_categories.category_id 
            AND ( cscart_categories.usergroup_ids = '' 
                   OR Find_in_set(0, cscart_categories.usergroup_ids) 
                   OR Find_in_set(1, cscart_categories.usergroup_ids) ) 
            AND cscart_categories.status IN ( 'A', 'H' ) 
       LEFT JOIN cscart_seo_names 
         ON cscart_seo_names.object_id = products.product_id 
            AND cscart_seo_names.TYPE = 'p' 
            AND cscart_seo_names.dispatch = '' 
            AND cscart_seo_names.lang_code = 'EN' 
WHERE  1 
       AND products.company_id = 0 
       AND ( products.usergroup_ids = '' 
              OR Find_in_set(0, products.usergroup_ids) 
              OR Find_in_set(1, products.usergroup_ids) ) 
       AND products.status IN ( 'A' ) 
       AND prices.usergroup_id IN ( 0, 0, 1 ) 
GROUP  BY products.product_id 
ORDER  BY descr1.product ASC 
LIMIT  1300, 50; 

我似乎无法从购物车公司获得有关如何加快此查询速度的任何帮助。也许我需要添加更多索引？我不确定，并且希望获得一些帮助，为我指明解决此问题的正确方向。

谢谢

克里斯·爱德华兹

Answer 1

我发现此查询存在很多问题，可能会导致速度缓慢...

首先，在任何使用“FIND_IN_SET”的地方，请尝试使用 IN 代替。通过删除条件中的“OR”，可以使用索引:

cscart_categories.usergroup_ids = ''
OR FIND_IN_SET(0, cscart_categories.usergroup_ids)
OR FIND_IN_SET(1, cscart_categories.usergroup_ids)

变成:

cscart_categories.usergroup_ids IN ('', '0', '1')

除此之外，请确保联接、group by 子句、where 子句或排序中使用的每一列都已建立索引。

另一个建议是删除“GROUP_CONCAT”并在另一个查询中单独选择该信息。