Optimize SQL-query

Question

I have following query:

Select diary_id, 
   (select count(*) 
    from `comments` as c 
    where c.d_id = d.diary_id) as diary_comments 
From `diaries` as d

It takes long time (near 0.119415 in my case). How to make it faster?

I see only one way: Doing additional query for comment number for each row from my main query. But it will be something like doing queries in cycle. Something like:

while ($r = mysql_fetch_array($res))
{
   $comments = mysql_query("select count(*) from `comments` where d_id = ".$r['diary_id']);
}

I think this is a bad strategy. Any other advice?

Tom Haigh · Accepted Answer · 2009-02-21 14:54:34Z

9

SELECT d.diary_id, count(c.d_id) as diary_comments 
FROM diaries d
LEFT OUTER JOIN comments c ON (d.diary_id = c.d_id)
GROUP BY d.diary_id

I seem to have been downvoted because you can actually retreive all the data needed from just the diaries table. I assumed that this was a simplified example and in reality other fields from the diaries table would be required, also this method brings back records which have no comments. If you don't need any of these two things then I would go with the other answer.

edited Feb 21, 2009 at 14:54

answered Feb 21, 2009 at 11:49

Tom Haigh

57.8k21 gold badges119 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ben Hoffstein · Accepted Answer · 2009-02-21 14:34:21Z

1

It looks like you have all the data you need in the comments table, so I don't see a reason for the join or subquery.

SELECT d_id AS diary_id, COUNT(*) AS diary_comments
FROM `comments`
GROUP BY d_id

answered Feb 21, 2009 at 14:34

Ben Hoffstein

104k8 gold badges107 silver badges121 bronze badges

1 Comment

Tom Haigh Over a year ago

good point. although he might want to display diary items which have no comments.

Community · Accepted Answer · 2017-05-23 12:19:01Z

1

Definitely plus one for tomhaigh's solution, a group by is exactly the thing in this situation.

One other option always worth remembering that you've got two choices here. Firstly to calculate the value on every read, and secondly to calculate it on every write and store the result. In the second case you would need an additional field on diaries called 'comments_count', this would need to be incremented on inserting a new comment. Obviously this could be less accurate than working out the count on every read and will slow your writes down. But if read performance is hurting and writes are noticeably less common than reads then this can be a handy way to consider the problem.

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Feb 21, 2009 at 21:20

Robin

2,62623 silver badges31 bronze badges

Collectives™ on Stack Overflow

Optimize SQL-query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related