CODEWITHSUNDEEP
mysqlperformance
brian14708
brian14708

Reputation: 1961

MySQL counting performance

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?

Upvotes: 3

Views: 132

Answers (4)

Eljakim
Eljakim

Reputation: 6937

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).

Upvotes: 1

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26739

Of course, caching the message count is faster. But if you have the right indexes, and assuming one user have no 1 million of messages, counting is fast enough. It of course depends on storage engine as well (column based storage engines for example are faster on aggragate functions) But assuming that you are selecting the user data every time, having the messages count in the same row saves you one query every time.

My advice is - go with counting unless it has a performance impact. When performance decrease because of counting, cache the count. When even cache is not enough, you can think about denormalizing the database. Premature optimisations are not useful, and the answer really depends on the scale of the application, and how often there are new messages.

Upvotes: 1

Jacob
Jacob

Reputation: 43299

It is not a good idea to have a numOfMsg field imo, as you have to keep track of the number in your programm. Anyway, MySQL optimizes COUNT(*) and I doubt it would be a bottleneck in your application.

Upvotes: 0

Jonathan Hall
Jonathan Hall

Reputation: 79774

That's a good question, and the answer likely depends on the scale of your application. Keeping a running tally is definitely going to make it easier/faster to retrieve those numbers when you need them, but it also makes your code much more complex, because you have to keep track every time a message is inserted, deleted, or moved between mailboxes/folders (if this is something your application will allow).

If you're willing to go to the effort to keep the count up-to-date, it's probably a good approach for that sort of thing.

I certainly use that sort of state-counter in code I write.

Upvotes: 1

Related Questions