Database Querying -- Better to count number of rows or better to keep an increment counter?

Question

If you were implementing a blog application - what would you prefer -
Having a counter in the "POSTS" table storing the number of comments

SELECT comment_count 
  FROM posts WHERE post_id = $id

...or counting the number of comments for a particular post from a "COMMENTS" table:

SELECT COUNT(*)
  FROM comments 
 WHERE post_id = $id

Which one is more optimized?

Answered here: http://dba.stackexchange.com/questions/239/storing-vs-calculating-aggregate-values — BenV, Jan 27 '11 at 05:15
@OMG: True, but the discussion of reads vs writes still applies. — BenV, Jan 27 '11 at 05:25
@BenV - My Question is a bit different compared to that one, I do not have to pre-calculate anything, the calculation is done by the SQL COUNT query. SO I was wondering which would be better to use. — Sussagittikasusa, Jan 27 '11 at 05:26

score 4 · Accepted Answer · answered Jan 27 '11 at 05:17

I would use the second form, COUNT, until I was sure that performance in that particular SQL query was a problem. What you're suggesting in the first is basically denormalization, which is fine and dandy when you know for sure you need it.

Indexes would allow you to perform the second query pretty quickly regardless.

score 0 · Answer 2 · answered Jan 27 '11 at 06:05

Let's look at the factors.

Case 1: When you display the post you display the comments. That means you retrieve them all and can count them as you display them. In that case, no.

Case 2: When you display the post you do not display the comments, but a link that says "15 comments." In that case there is an equation.

Materializing the count:

Cost of one comment save = 1 Insert + 1 Update

Cost of one post display = 1 Read

Average Number of blog displays = D

Average Number of Comments = C

So, for what ratio of Displays D vs. Comments C is it true that:

C * (Insert + Update) < D * (Read)

Since it is usually true that D >> C, I would suggest that the cost of that extra update vanishes.

This may not be so important for a blog, but it is a crucial formula to know when you have many tables and need to make these decisions.

Database Querying -- Better to count number of rows or better to keep an increment counter?

2 Answers2