What is the best way to handle user statistics in PHP?

Question

How can I handle user statistics in PHP?

There are two obvious methods that I can choose. Both have their drawbacks.

• If necessary, select MySQL COUNT. The disadvantage is that if you have a lot of lines to count, then this can be slow, especially when you have to do this, apparently, on every page load. The advantage is that the score will always be correct.

• Save user statistics in the statistics table. The downside here is that you have to keep updating it whenever changes occur, and this makes the code overly complex if you need to update it massively. The advantage is that it will quickly select a single line of statistics for the user, rather than perform calculations.

Another possible method by which I am a little “e” is storing the job in the queue (and Laravel processes it). These tasks will update the statistics needed using other tables so that they synchronize properly. The advantage is that it removes the load from the web server, and the disadvantage is that the user may receive incorrect statistics. It is not advisable for your own friend list to say that there are, for example, 15 friends and 7 friend requests, when the actual numbers are very different.

I examined in detail the methods that I came up with, and I'm not sure what best gives the right results for the user, as well as the speed and ease of balancing. If I execute the COUNT method, then I can potentially cache the result and delete the cache entry if the statistics are updated, but I would suggest that saving the line in the cache table for the EACH user went a little too far. Maybe this is not a problem if the database has enough space, but, of course, searching in a massive cache table will still be slow?

Perhaps someone can give me a better choice for handling user statistics. My head is spinning when she changes her mind and I need to be straight and narrow.

Thanks in advance.

Answer 1

Do not exaggerate the cost of COUNT(*) when planning this part of the application. If you have the correct index in the table, row counting is very fast. In fact, if your table is MyISAM, it can be O (1) in complexity.

For example, if you have an index on user , the SELECT COUNT(*) AS num FROM friend WHERE user = ' [e-mail] ' query will be very fast.

Create an application in an easy way. When you have ten thousand users, you can redesign such a statistical calculation to be more complex and efficient. When you have more users, it will not be so obvious if you give approximate results.

Be careful. COUNT (*) is much faster than COUNT (expression) in most cases. * Allows MySQL to avoid evaluating each row.