CODEWITHSUNDEEP
mysqldatabaseperformancerecord-count
john
john

Reputation: 1280

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

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?

Upvotes: 8

Views: 2914

Answers (4)

Mark Wilkins
Mark Wilkins

Reputation: 41252

It is a trade-off in cost and complexity. By maintaining the count in the user table, it adds some complexity to keep it accurate, and it adds cost to inserting/deleting votes. It means that adding a vote then requires (at least) updating two tables.

So it depends a little on which piece needs to be the most efficient. If retrieving the vote count is performed an extremely large number of times, then perhaps it makes sense to maintain the count.

In my opinion, though, it would be better to go with the simpler implementation first and assume that the database will be able to optimize the query and make it a non-issue. If that turns out not to be fast enough, then make the changes to add the pre-computed count later.

Upvotes: 0

Starx
Starx

Reputation: 79031

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.

Upvotes: 3

Peter
Peter

Reputation: 16943

In proper configuration (default configuration is good enough in most cases) MySQL 5.0+ server is caching SUM,COUNT queries, so MySQL is handling that sort of queries automatically.

But if you are using older version (MySQL 4 or less) i recommend to store COUNT(*) values in database, beacause it really cause on perfomance on bigger tables.

Edit: Best practice i discovered is making an COUNT(*) query every time user is adding/deleting vote/comment etc. Modern SQL servers are handling group queries very well, so we don't have to bother about perfomance.

Upvotes: 2

zerkms
zerkms

Reputation: 255015

Precalculation is one of the often denormalizing optimizations.

So just create the precalculated column and maintain it with triggers or your application code.

As @Bohemian pointed out: you need to do that only if you have performance issues.

Upvotes: 0

Related Questions