COUNT() function each time, or store the value and increment it by one?

Question

I have a database with a user 'votes' table and a 'user' table. I'm thinking the database will get quite big in a small amount of time so i want to use the most efficient method.

I'm thinking i can either COUNT() the amount of votes with a WHERE statement from the 'votes' table every time, or i can store the score in the 'user' table and just increment it by 1 every time a vote is added.

Which would be best/quickest, and/or are there any other ways of doing this?

Answer 1

If you are thinking of the best way to do it. You have to look into optimizing and caching a lot.

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

Whenever score changes operate of scores table and trigger an update on user's table with the latest score result.

Doing this, you have extendability in your score data to, kind of like what stackoverflow uses for votes.