MySQL counting performance

Question

If I was making a message application (e.g. email), and I had to count the number of messages.

Would I be better off counting the message every time, or should I make a new column called numOfMsg and increment it when a message is received.

EDIT:

It seems like phpBB http://wiki.phpbb.com/Table.phpbb_topics stores the reply numbers in the database, does anyone know what their intention was?

Answer 1

You can just use the MySQL function COUNT() to count the messages. If you use the appropriate indices this is very very fast. (If you do a count by user+box you will want to have a combined index on user+box)

Note that MySQL will also cache the results of your queries, so as long as no new messages have arrived [your message table is unchanged] it won't even go back to memory/disk to do the actual count; it will just return the last value. So a very cheap operation.

The trouble with keeping extra redundant information is that it may be very hard to keep these up to date; you can ADD or REMOVE messages; some users may MOVE messages between boxes, and all this time you have to keep the counters correct. You would also have to start using transactions to ensure that the INSERT of the message and the UPDATE of the counter are either both done or both not done (for instance, when you lose connectivity, or something crashes).