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

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 2

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