Efficient way to display Inbox(1) count in a webapp w/o calling DB for each page request

Question

In my web application we have built a message center / inbox functionality, in the navigation of each page we link to the "Message Center" and include next to it a count of the unread messages, for example "Message Center (2)". To populate the (2), with each request we run a *SELECT COUNT(*) FROM MessageTable WHERE unread = true blah blah to get the count to include in the navigation.

This means that for each page load we are hitting this table and querying for a count.

I've come up with two alternatives:

• Keeping the aggregate count elsewhere in the database rather then calculating the count each time (also taking the load off the message table). This still means hitting the database w/ each request.
• Setting up a temporary queue destination per logged in user and sending updates to this queue if the user is logged in which can increment / decrement a count stored in the session. (not sure if this is even possible)

Are these good alternatives, are there other options?

Answer 1

So the number is the unread messages?

Maybe you can save that number to user's cookie and update it when

• each time user checks new messages
• user read a message (this can be done in javascript or server script)
• timely basis

Answer 2

One suggestion as mentioned in your post is using a different MessageCounters table with something like [UserId : NumberOfMessages]. Indexing UserId will make this a cheap operation (and it'd only trigger a write every time a new message arrives). The downside is that for every new message you'll be doing two different writes (one to MessageTable and another one to MessageCounters.

Other options you can consider if you really think that this is will be a problem are:

• Store this data on Memcached and retrieve it from there. If you're at the scale where those queries worry you, you'll probably be using Memcached anyway.

• Use a separate persistent K/V storage facility like Redis or TokyoCabinet for some of the simpler tasks of the website (like keeping this count).