Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Let's take CodeReview page as an example. If you go here, you will see the list of the questions with their vote numbers and comment numbers

Now, I am wondering if these numbers are calculated on the fly (e.g. count the total comments in db for this question), or is there an actual field in the questions table called voteCount and it's incremented by one whenever someone votes for this question?

Just another question. Both VoteCount and CommentCount are just integers, what about the user information that is displayed for each question?

Hope this is the correct place to ask this question.

share|improve this question
    
boosis I edited out references to Zend Framework (post text & tag) because the question really has nothing to do with it. If it did, i.e. was an implementation specific question, it would probably be off topic here. Check out Database Administrators SE, I think you'll find quite a few relevant posts there. –  Yannis Rizos Jan 11 '12 at 23:06

1 Answer 1

up vote 3 down vote accepted

It really depends on the traffic of the site, and you should probably don't bother yourself with such decisions unless:

  1. You've identified an actual bottleneck
  2. You've identified an actual bottleneck on a very similar scenario in the past

Premature optimization is the buzzword, and Donald Knuth describes it1 better than I could possibly:

Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.

The simplest approach is to have a vote table, where you log all votes. Typical fields would be voteID, voterID, questionID, voteTimestamp, and you could calculate the totals on each request. But, that will probably become a performance issue very soon, especially with a high traffic site as StackOverflow2.

In that case, my approach would be to run a scheduled background process3 that calculates totals and stores them in a different table (or the question table), and possibly even in a document storage database4. Or, even in a memory cache5, if it makes sense.

There are other ways to cache calculated totals, these are the simplest ones (I think).

As for the user information, they are expected to change less often, so you could probably get away with caching them, without any special database realm approach.

Generally speaking reads are quite faster than writes and in common low traffic scenarios you'd be just fine by just sensibly caching your views. There is no definitive approach, mix and match as you identify bottlenecks.

1 Warning! PDF link.
2 We can't really isolate CodeReview, it's based on the common platform, if something is a bottleneck on StackOverflow, by default it is (or will be) on CodeReview.
3 Cron is your best friend!
4 Assuming the core database is relational.
5 Memcached is your other best friend!

share|improve this answer
1  
Thanks for the reply Zaphod, but I really think this is a very important decision to make during the initial development. As you know db queries are the most common bottleneck of all. Let's say if I decide to calculate the votes on the fly, it means if I display 10 posts per page it means 10 extra db query (assuming there is no caching at all). But if I keep the counters in the same table as the post, there won't be any extra query needed. –  Optimus Jan 11 '12 at 22:48
    
I think keeping the vote count/comment count (or any similar integer counters) in the same table as the post sounds better solution. But what if I need to display the user information for each post (i.e. authorname)? of course authorid will be in the posts table but to get the author name, I need to go back to db for each post (again, assume no caching) –  Optimus Jan 11 '12 at 22:51
    
Sorry, I didn't see the second part of your answer :) instead of cron job, would it be better to increase the VoteCounter field in the questions table whenever user up votes it? If cron job is used, again it needs to scan a huge table to calculate the counters –  Optimus Jan 11 '12 at 22:56
1  
@boosis, you would need to still do both. By storing it on your Q table as well as storing the vote itself (which you need to do unless someone can voting as much as they want on each Q) you are denormalizing your database. That means edge cases can cause explicit votes not to match the stored counter, as well as infrastructure issues, etc. Because of this you will need a method to reconcile your information - much in the same way StackExchange sites have rep-recalcs. –  Dan McGrath Jan 11 '12 at 23:10
1  
I really think this is a very important decision to make during the initial development. I know from experience that generally it's not, but unless I have a very specific idea of what you're building I can't say if that applies to your situation. instead of cron job, would it be better to increase the VoteCounter field in the questions table whenever user up votes it? If cron job is used, again it needs to scan a huge table to calculate the counters It highly depends on what you are building. Define huge, how many millions of rows are you talking about? –  Yannis Rizos Jan 11 '12 at 23:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.