Optimizing a slow query

Databases
1

Hi all!

I am trying to optimize this slow query. It keeps showing up in my slow query log. :frowning:

SELECT b.*, bc.blog_id, COUNT(bc.blog_id) AS numcomments FROM blog b
LEFT OUTER JOIN blog_comments bc ON b.id = bc.blog_id GROUP BY date DESC LIMIT 0, 5;

It selects all of the blog posts 0, 5 and counts the number of comments associated with each post. We have about 60,000 comments on our blog, so the query is getting clunky. There is an INDEX on bc.blog_id field in the comments table, btw.

Any ideas as to how I can optimize this? Thanks.

2

correct :slight_smile:

3

why do you have GROUP BY DATE?

i thought you wanted the count of comments per blog, not the total number of comments on all blogs by date

4

Group by date (there’s a column titled “date” in blog b) is to order the blog posts by date… so the most recent are shown at the top of the list.

i thought you wanted the count of comments per blog, not the total number of comments on all blogs by date

And yes, you are correct – am I doing the query wrong in light of this goal? :frowning:

5

Ahhh – thanks so much. This is exactly it and what I need. Thanks again.

Just for my own personal learning knowledge: you use COALESCE to make sure that, in the event that there are no comments, numcomments evaluates to 0, correct?

6

the purpose of GROUP BY is to aggregate, to collapse a number of rows down to one result row

you were thinking of ORDER BY :slight_smile:

SELECT b.*
     , COALESCE(bc.cmts,0) AS numcomments 
  FROM blog AS b
LEFT OUTER 
  JOIN ( SELECT blog_id
              , COUNT(*) AS cmts 
           FROM blog_comments 
         GROUP
             BY blog_id ) AS bc 
    ON bc.blog_id = b.id
ORDER 
    BY b.`date` DESC LIMIT 0, 5