Is it better to maintain a separate counter table each time and run the counter?

Question

I am creating a social app that adheres to the following concept similar to Twitter.

In terms of performance, to find no.t followers and next users, is it better to maintain a separate table for counting? or just do a count request every time?

Update:

Likewise, I have a review function in which people can vote, people can only vote “Yes” or “No”. Now I store voices in a separate table. And I need to show a list of polls without participants, no and no on my home page.

Similar to the stackoverflow homepage (where they show the number of votes, replies and views).

Answer 1

This, like most things, depends on access patterns, that is, on how you use your system. If the update will be your main bottleneck, then you should not incur additional overhead, having to maintain a counter. If, on the other hand, when accessing data that has readiness for counting, you will save considerable time or it will just be impossible to count every time, then you must first clear it.

As a general guide, do not add tables, as a separate counter table is suggested, which is offered solely for performance optimization, before you actually rate performance as a problem. Having a separate counter table violates normalization (as any caching does, since the data is now replicated in two places) and will make the code more complicated, so it should not be done just because the account may be needed.

(All that is said, some databases support materialized views / materialized queries that allow you to easily execute this kind of caching transparently in the background. These materialized tables are updated by the database, so the program code should not worry about it, as well, depending from the complexity of the query optimizer, can be used to optimize a transparent query.)

Update: The question of voting "No / Yes" is slightly different, since the main goal is to simply track the account, and not all the information (i.e. Who voted "yes"). Thus, the actual implementation may consist in simply tracking the accumulated number of yes and no votes. However, the more information you store (i.e., who voted yes, and not just a lot), the more you can do with it if you do (for example, in Stackoverflow I can always remove my top position - something that you could not do if you did not track who voted). Again, I would advise you not to combine at the beginning, in this case, because you will lose certain information.

Answer 2

It depends.

If you have many users, the counter can be quite long and load large parts of the table / indexes into memory.

If you make a trigger, you will lose some time during the wrting process, so each subsequent action will be slower.

The combination between them, the asynchronous submission of a statistical table of followers, can give you the best results (fast in write operations, very fast in reading).

Answer 3

Alternatively, you can use two data containers:

• A normalized database for complete data that you read when you want to display full profile data
• A search index (e.g. Solr / Lucene) with the most frequently displayed data, including aggregates such as the counts that you use to quickly display and search