mysql - 如何使用多个连接和子查询优化这个慢速查询

Question

我必须优化别人写的一个查询，我很挣扎。有时需要超过 12 秒才能完成!

我有一个 SQL fiddle here ，但是那里还没有数据。我认为数据量部分是造成速度缓慢的原因(wed_supplies + 邮政编码中有 20k，评论中有 60k)，还有 num_reviews 和 avg_rating 子查询。

去掉子查询将其缩短到大约 2 秒，但我需要它们提供的值，而且还需要更快。

SELECT *, c_title AS category,

        (SELECT COUNT(*) FROM comments WHERE site_id = '96' AND ec_type = 'review' AND ec_link_id = ws_id) AS num_reviews,
        (SELECT AVG(ec_rating) FROM comments WHERE site_id = '96' AND ec_type = 'review' AND ec_link_id = ws_id) AS avg_rating,
        (((acos(sin(( 52.1528253 *pi()/180)) * sin((`p_lat`*pi()/180))
            +cos(( 52.1528253 *pi()/180)) * cos((`p_lat`*pi()/180))
            * cos((( -0.6800496 - `p_lng`)*pi()/180))))*180/pi())*60*1.1515)
            AS distance
        FROM wed_suppliers 
            LEFT JOIN postcodes ON p_postcode = REPLACE(ws_postcode, ' ', '')
            LEFT JOIN c_content ON ws_category = c_id
        WHERE wed_suppliers.site_id = '96' AND c_content.site_id = '96'
        AND ws_hide = '0'
        AND ws_permalink != ''
        AND p_lat != '' AND p_lng != '' AND p_invalid = '0'
        HAVING distance <= 10

        ORDER BY distance ASC
        LIMIT 0,20

解释

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     wed_suppliers   range   ws_permalink,site_id,ws_category,ws_hide    site_id     4   NULL    22628   Using where; Using temporary; Using filesort
1   PRIMARY     postcodes   eq_ref  PRIMARY,p_invalid,p_lng,p_lat   PRIMARY     12  func    1   Using where
1   PRIMARY     c_content   eq_ref  PRIMARY,site_id     PRIMARY     4   engine_4.wed_suppliers.ws_category  1   Using where
3   DEPENDENT SUBQUERY  comments    index_merge     site_id,ec_link_id,ec_type  site_id,ec_type     4,34    NULL    1   Using intersect(site_id,ec_type); Using where
2   DEPENDENT SUBQUERY  comments    index_merge     site_id,ec_link_id,ec_type  site_id,ec_type     4,34    NULL    1   Using intersect(site_id,ec_type); Using where

我用过this glossary EXPLAIN 返回的内容，但运气不佳。

我如何优化此查询以使其以更合理的速度运行？以及我如何将 EXPLAIN 转换为更有用的内容。

Answer 1

看起来您的联接正在使用索引。这留下了子查询:

    (SELECT COUNT(*) FROM comments WHERE site_id = '96' AND ec_type = 'review' AND ec_link_id = ws_id) AS num_reviews,
    (SELECT AVG(ec_rating) FROM comments WHERE site_id = '96' AND ec_type = 'review' AND ec_link_id = ws_id) AS avg_rating,

我推荐以下复合索引:comments(ec_link_id, ec_type, site_id)。这应该会加速子查询。