Optimize SQL query

Question

I have the 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 

This takes a lot of time (about 0.119415 in my case). How to make it faster?

I see only one way: Perform an additional request for the comment number for each line from my main request. But it will be like executing queries in a loop. Sort of:

 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 tips?

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 

It seems that I was blocked because you can really get all the data you need from the diary table. I assumed that this is a simplified example, and in fact, other fields from the diary table will be required, also this method returns entries that have no comments. If you do not need either of these two things, I would go with a different answer.

Answer 2

It looks like you have all the data you need in the comment table, so I see no reason for the connection or subquery.

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

Answer 3

Definitely a plus one for tomhaigh solution, and that is exactly what is in this situation.

Another option is always worth remembering that you have two options. Firstly, to calculate the value for each read, and secondly, to calculate it at each record and save the result. In the second case, you will need an additional field in the diaries with the name "comments_count", this will need to be increased when adding a new comment. Obviously, this may be less accurate than calculating the score at each reading and slowing down the record. But if read performance is degraded and records are noticeably less common than reading, then this can be a convenient way to deal with the problem.