How to make an action counter?

Question

Laravel

What is the best way to make a counter of any user actions, for example, leaving comments, for further output to the TOP 100, for example.
I think it would be correct to simply count the number of entries in the comments table for each user, but with the number of users 100,000+ and the same number of comments, this will not happen quickly.
It is possible to make an additional field comments_count in the users table and increase it when writing a comment, but this is not very correct in my opinion.
Tell me how best to implement this, provided that the number of users and comments will be over 100k

Answer 1

It is possible to make an additional field comments_count in the users table and increase it when writing a comment, but this is not very correct in my opinion.

they do it, except that they increase it asynchronously
, think for yourself - an expanded project, and now your base with a counter in you yourself understand where

Answer 2

It is strictly forbidden to use any "comments_count" - they only cause problems. Always!
The most correct and simplest solution would be to cache the request after each comment or any other action. Thus, we do not change anything in the code, but at the same time we give a huge performance boost when it comes to millions.
In general, when it comes to such figures, it is not very clear why everything is on a single host with a single database ..

Answer 3

Cache the count of the desired entity for each user in his model, or wherever you think is more correct.
There are several ways to update it. You can create a queue into which the job will periodically fall after a certain user action. You can set the time intervals for updating the rating, for example, 4 times a day, and at intervals of 6 hours, slowly update the count for each user (in this case, we will cache the old and new values ​​​​and their time, and update the rating when everyone has a new value within the required range).