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?
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.
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.
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.
链接地址: http://www.djcxy.com/p/59182.html上一篇: 强制install.packages（）