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?

Answer 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.

Answer 2

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.

Answer 3

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