How can I optimize this slow query with multiple joins and sub queries

Question

I have to optimise a query that someone else wrote, and I am struggling. Its taking sometimes in excess of 12 seconds to complete!

I have an SQL fiddle here, however theres no data in there yet. I think the amount of data is partially the cause of the slowness (20k in wed_supplies + postcodes, 60k in comments), as well as the num_reviews and avg_rating subqueries.

Taking out the subqueries takes it down to something more like 2 seconds, but I need the values they provide, and it needs to be much faster still.

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

EXPLAIN

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

I have used this glossary of what EXPLAIN returned, but not having much luck.

How can I optimize this query so it runs at a more reasonable speed? And how I can translate the EXPLAIN into something more useful.

if you can modify the database schema, you may review your indexes — heringer, Jul 17 '14 at 16:06
@heringer the indexes are in the sqlfiddle link I posted in the top — Horse, Jul 17 '14 at 16:18
@Horse - Tables are not big by any means. Can you try 1. cleaning the data before running the query thus avoiding REPLACE function in join? 2. Create temp tables for subqueries and then join it with main query? — Shiva, Jul 17 '14 at 17:22

score 1 · Answer 1 · answered Jul 17 '14 at 16:09

1

It looks like your joins are using indexes. That leaves the subqueries:

    (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,

I would recommend the following composite index: comments(ec_link_id, ec_type, site_id). This should speed the subqueries.

answered Jul 17 '14 at 16:09

Gordon Linoff

1,242,037
58
646
786

I added the following but it made very little difference execution time - ALTER TABLE `comments` ADD INDEX `comments_comp` (ec_link_id, ec_type, site_id) – Horse Jul 17 '14 at 16:19

How can I optimize this slow query with multiple joins and sub queries

1 Answers1