Best way to update a comment count etc.

[samarudge]

Hi guys,
So I am developing a website which will allow users to comment on things. As part of this I need to display a comment count and I wanted to know what you thought was the best way of doing this:
The two options are
Every hour/day run a CRON that counts the comments on a certain item and then updates the articles DB entry
Or
As soon as a comment is added +1 to the comment count

I should mention that this will be a highly used site (estimating 1,000,000+ hits per month)

Which is the best way of doing it?
Thanx,
Sam Rudge

[tchaves]

Both of those seem to have accuracy problems... why not count the comments when the page is loaded? I assume the comments are their own table in the database?

$query = "SELECT comment_id FROM comments WHERE article_id  = '$article_id'";
$result = @mysqli_query($connection, $query);

$num_comments = mysqli_num_rows($result);

[samarudge]

Yes I know I can do that but there are two reasons why I wouldn't:
1) The comment count would be displayed on the search results as well so it would considerably slow search page loading down and the servers would be under grate strain anyway
2)

[samarudge]

Yes but
1) The comment count will be displayed on search pages (So calculating that for each result would slow page load and put the server under even more strain)
2) If thousands of people are viewing the same page there would be a large load on the DB server.

[tchaves]

Very valid. In that case I would adjust the comment count so long as it gets adjusted for deleted comments as well. The cron doesn't seem to cut it, especially with hundreds of thousands of readers who will expect accuracy.