CODEWITHSUNDEEP
mysqlselectindexingleft-joinquery-optimization
sezarsaman
sezarsaman

Reputation: 29

Slow mysql query including Left Join and Selects

SELECT SQL_CALC_FOUND_ROWS null as `rows` , 
   (SELECT count(*) FROM `ci_user_smiley` where post_id = P.post_id) as smiley, 
   (SELECT count(*) FROM `ci_user_tag` where post_id = P.post_id) as tag, 
   (SELECT count(*) FROM `ci_comments` where post_id = P.post_id) as comment_count, 
    P.*, U.point_1 + U.point_2 + U.point_3 as `total`, F.*, Fi.*, U.* 

FROM `ci_posts` P 

LEFT JOIN `ci_users` U 
     ON P.`user_id` = U.`user_id`
LEFT JOIN `ci_filters` F
     ON P.`filter` = F.filter_id 
LEFT JOIN `ci_files` Fi 
     ON P.file = Fi.file_id 

WHERE P.`approve` = 1 AND U.active = 1 AND P.post_type = 'post-color'
     AND 1 ORDER BY P.post_date DESC LIMIT 0,20

This query takes 5 minutes to execute, How can I improve it!? Is that because of LEFT JOINs or Selects? Notice that Some of tables has got between 10K to 100k rows I appreciate any suggestion!

Upvotes: 1

Views: 79

Answers (1)

O. Jones
O. Jones

Reputation: 108651

A few things to consider to optimize this.

Overall, read http://use-the-index-luke.com/

First: Your subqueries like this one will be faster with an index on post_id.

SELECT count(*) FROM ci_user_smiley where post_id = P.post_id

Second: A wise programmer strenuously avoids using SELECT ... table.* or any form of SELECT column wildcard * character, instead giving a list of needed columns for the query. The query planner can often optimize a query when it knows it can omit some columns from the result set.

Third: This is a notorious query antipattern.

 SELECT lots of columns
   FROM table JOIN table .... lots of tables
  ORDER BY column
  LIMIT small number

Why? It tells the query planner to generate an enormous result set, sort it, and then discard all but small number rows of it. This is wasteful.

You may do better with a query containing something like this

 WHERE p.post_id IN (SELECT p.post_id 
                       FROM ci_posts p
                       JOIN `ci_users` u 
                                 ON p.`user_id` = u.`user_id`
                      WHERE p.approve = 1
                        AND p.post_type = 'post-color'
                        AND u.active = 1
                      ORDER BY p.post_date DESC LIMIT 20)

The IN clause fetches just the twenty interesting post_id values. That will limit the load / sort / discard operation to just the post_id and post_date columns, which will be far cheaper. A compound index on ci_posts (post_type, approve, post_date, user_id) will help a lot.

I've used JOIN instead of LEFT JOIN because the u.active = 1 clause turns the LEFT JOIN into a JOIN anyway.

Upvotes: 1

Related Questions