Which version of the query logic is correct?

Question

PHP

The page displays the last 10 records from the database. For each of these entries, you need to display the number of comments from the `comments` table.

1 option. When selecting records, count comments for each. It turns out 1 request for selection + 10 for counting = 11 requests.
Option 2. When adding a comment, increment the `count_comments` field in the table of entries by 1. It turns out only 1 request for a selection.

Which of these options is more preferable. Or maybe there is a third one?

Answer 1

LEFT JOIN and GROUP BY.
Something like

SELECT
   r.id,
   r.record,
   COUNT(c.id) CountOfComments
FROM records r LEFT JOIN
    comments c ON r.id = c.record_id
GROUP BY r.id

Answer 2

Another option:
1st request for a selection of comments;
2nd request of the form:

SELECT COUNT(c.id) CountOfComments
FROM comments
WHERE c.record_id IN (... IDS ...)
GROUP BY c.record_id

In IN, you simply list the IDs of the records obtained from the 1st query.
In general, I think that with intensive requests for a selection of all records, the 2nd option is quite suitable.

Answer 3

In my opinion, the second option is preferable. It will completely save you a headache in the future when the amount of data may increase. It is more efficient to spend computing resources at moments that occur less often - in this case, adding / deleting comments occurs much less frequently than viewing them.
You can implement this logic using mysql triggers, which will automatically update the number of comments with each addition / deletion Full dump here
CREATE TRIGGER `comment_add` AFTER INSERT ON `post_comments`
FOR EACH ROW BEGIN
SET @post_id = NEW.post_id;
SET @comments_num = (SELECT COUNT(id) FROM post_comments WHERE post_id = @post_id);
UPDATE `post` SET `comments_num` = @comments_num WHERE `id` = @post_id;
END;


Answer 4

correctly, from the point of view of normalization, option No. 1, when there is no duplicate and dependent information. If this is not a performance-critical place, then it is better not to create unnecessary entities.
But if a request of this kind is often used or is slow, then it is worth introducing an additional field. IMHO, it's better to make a trigger for changes in the table with comments and update the field in the table with posts in it, make it default to 0 and generally forget about modifying it in the application.

Answer 5

Records in other table are stored? And on what fields communication between tables is realized?

Answer 6

Thank you. I will test different options.

Answer 7

Thank you. I will test different options.

Answer 8

Double-edged sword. Obvious and most adequate options:
1. Select posts, select the number of comments to them. Total 2 SELECT requests for display.
2. Use LEFT JOIN and GROUP BY. Total one, but slow display request.
3. Add a comments_count column to the posts table. There will be one quick query per display, but one UPDATE (or trigger) will be added to insert a new comment, plus you will need to carefully monitor this value (decrease when deleting a comment, etc.).
If the load is not very large, I would choose the first option as the easiest to implement and maintain. With an increase in load, I would switch to the third.

Answer 9

Only the second option. Don't listen to the theorists. MySQL does not optimize all these joins and aggregation in any way.

Answer 10

Only the second option. Don't listen to the theorists. MySQL does not optimize all these joins and aggregation in any way.

Answer 11

Let me share my experience… in the good old days, when I was still small, I had to count the number of comments on the news. I did not like Joyna and applied the first option. Then the statistics increased, fields were added, the algorithms became more complicated. As a result, 10-15 selects were performed per article. Then it turned out that it was necessary to show not 10 articles, but 50 - a total of about 500 requests per html page. And I didn’t need it already, but the main programmer, and he, without thinking twice, decided to simply call my method, without looking at how it was implemented. Well, you can imagine his reaction when he looked at how fast sql writes queries to the log file :)
Draw your own conclusions.

Answer 12

Definitely the second, and without various triggers in the database, otherwise a new developer can scratch his head for a long time thinking why there is no place in the code where the comment counter changes.

Answer 13

Definitely the second way.
If you use ORM, then in the comment model class, add a counter increment to the database in the save method, and decrease it in the delete method.