Re: How would you store read/unread topic status?

On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra<mateo21(at)siteduzero(dot)com> wrote:
> The flags table keeps track of every topic a member has visited and
> remembers the last answer which was posted at this moment. It allows the
> user to come back a few days after and immediately jump to the last
> answer he has not read.

I forgot to mention that we speed up our queries by caching the "last
read" ID in Memcached. This is the kind of thing that Memcached is
ideal for.

For example, we show the list of the most recent posts, along with a
comment count, eg. "42 comments (6 new)". We found that joining posts
against the last-read table is expensive, so instead we read from
Memcached on every post to find the number of unread comments.

We use the thread's "last commented at" timestamp as part of the key
so that when somebody posts a new comment, every user's cached unread
count is invalidated; it is automatically recalculated the next time
they view the post.

A.