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?

Answer 1

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.

Answer 2

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.