SQL update

[Readie]

On a news post/comment script I'm working on I want to have a link to the comments saying something like "30 comments on this article" - now my question is how should I go about getting the "30"?

One fool-proof method would be to simply do

$sql = 'SELECT COUNT(*) AS total FROM news_comments WHERE news_id = ' . $someVariable;

But I'm worried that might be a little CPU intensive, so I think a column on the news posts table with a count of the comments would be more effective.

My concern is users posting comments within just a few milliseconds of each other.

With a script like (semi pseudo code)

if(submit button pressed / validation passed){
// Do comment insert
$sql = 'SELECT t_comments FROM news WHERE news_id = ' . $someVariable;
$res = mysql_query($sql);
$total = mysql_result($res, 0, "total_comments");
$total += 1;
$sql = 'UPDATE news SET t_comments = ' . $total . ' WHERE news_id = ' . $someVariable;
mysql_query($sql);
}

Is there any risk of 2 users adding comments so close together that this fails and only adds 1, when there are 2 new comments?

[syber]

It shouldn't be CPU intensive if you have an index on news_id.

[Anyango]

Is there any risk of 2 users adding comments so close together that this fails and only adds 1, when there are 2 new comments?

None, not that i know of

[Tommybs]

Change the sql to "select count(news_id)" as well if that's your index. Saves select * back and all the extra fields that aren't necessary if you want to optimize a bit more.

[Readie]

Thanks for the replies :)

So, the options are an optimised version of what I know 100% is fail safe, or something that we're pretty sure is fail safe.

I think I shall use "COUNT($news_id) AS total" however, just to be on the safe side.