COUNT () every time, or save a value and increase it by one?

Question

I have a database with a user votes table and a user table. I think the database will be large enough in a small amount of time, so I want to use the most efficient method.

I think that each time I can either COUNT () count the number of votes using the WHERE statement from the "votes" table, or I can store the score in the "user" table and simply increase it by 1 each time a vote is added.

What would be better / faster and / or are there other ways to do this?

Answer 1

If you are thinking about how to do it. You have to look a lot in optimization and caching.

I would say Create a column in user tables to store the cached score, but keep the score in a separate table.

Whenever counter changes work with the score table and cause the user table to be updated with the latest score.

By doing this, you have the option of expanding in your assessment data, such as what stackoverflow uses to vote.

Answer 2

In the correct configuration (in most cases, the default configuration is good enough) MySQL 5.0+ server caches SUM, COUNT requests, so MySQL processes such requests automatically.

But if you are using an older version (MySQL 4 or less), I recommend storing the COUNT (*) values ​​in the database, because it really leads to the execution of large tables.

Edit: The best practice I discovered is making a COUNT (*) request every time a user adds / removes a vote / comment, etc. Modern SQL servers handle group queries very well, t have to worry about performance.

Answer 3

Preliminary costing is one of the often denormalized optimizations.

So, just create a pre-calculated column and save it using triggers or application code.

As @Bohemian noted: you only need to do if you have performance problems.

Answer 4

This is a compromise in cost and complexity. By storing the score in a user table, it adds some complexity to keep it accurate, and this adds the cost of inserting / removing votes. This means adding voice then requires (at least) updating the two tables.

So it depends a little on which part should be the most effective. If getting the vote count is done extremely many times, then it may make sense to maintain the score.

In my opinion, it would be better to first switch to a simpler implementation and assume that the database can optimize the query and make it invalid. If it’s not so fast, make changes to add a pre-calculated score later.