num_rows Faster than Counter?

[avario]

I have a MySQL database that stores all messages for all topics and a database that stores information about the topics...

On the any given topic page I want it to display the number of messages in that topic, this value would only need to be retrieved once when the page is first loaded.

Would it be faster and more efficient to:

• Every time a user requests the topic page it does a query to count the number of messages that are in that topic. Such as:

  $messages=mysql_query("SELECT * FROM messages WHERE topic='$topic'");
  $numMessages=mysql_num_rows($messages);

• Or store the number of messages in the topics database? Such as:

  $topic=mysql_query("SELECT nummessages FROM topics WHERE id='$topicid'");
  $numMessages=mysql_result($topic,0);

  And then update this value every time a message is posted.

I would prefer the first message because messages are posted live and I would like this to happen as quickly as possible without having to update the nummessages value of the topics database, but if it is a slow method then I wouldn't mind doing the other one.

Also, I an expecting there to be around a million messages in total, a thousand messages for each topic and so a thousand topics.

Thanks for any help and recommendations.

[mschroeder]

Your first example would require returning a potentially massive data set from the database simply for a count so I wouldn't suggest that.

I've seen the second one mentioned when dealing with massive datasets as well. Usually in conjunction with a set of triggers that when an row is inserted automatically increment the count and when a row is removed automatically decrement the count. This would be best stored in a separate table that contained only count values in my opinion.

Your other option would be the COUNT() function. Although with out benchmarking it I don't know if it would be any faster or slower than your first proposal.

[avario]

Thanks for the reply mschroeder,
I think that the COUNT() function is the best way to go because unlike SELECTing all of the rows and then num_rowing them the COUNT() function does not need to actually select the data, only count it.

Thanks.

[mschroeder]

Except count() is approximate when used on innodb tables, possibly all transactional table types. Just something to keep in mind.

[avario]

Ok, thanks.

[mohammed yaghi]

Sure The first way >>>>
Look There is Rule in DBMS (data bas management system) as follow : -
The fields like number of students , numbe of message , age and so on
recommended to calculated , not to considered as fields in table
thanks

[avario]

Thanks mohammed yaghi,

I think that that is completely correct, the COUNT function should comply with this rule.