Determining unread items in a forum

Question

Using PHP and MySQL, I have a forum system I'm trying to build. What I want to know is, how can I set it so that when a user reads a forum entry, it shows as read JUST for that user, no matter what forum they are in, until someone else posts on it.

Currently, for each thread, I have a table with a PostID, and has the UserID that posted it, the ThreadID to link it to, the actual Post (as Text), then the date/time it was posted.

For the thread list in each forum, there is the threadID (Primary Key), the ThreadName, ForumID it belongs to, NumPosts, NumViews, LastPostDateTime, and CreateDateTime. Any help?

Answer 1

There's no easy way to do this. There are plenty of ways to do it, but each grows exponentially larger as the user visits the site. The best you can do and still keep performance is to have a timestamp and mark any forums that have been updated since the last visit as 'unread'.

Answer 2

So another entry in the Users table maybe for last time entered into the forum system... OK

And why did someone mark this as -1? It's a valid question, seen in other forum software, and I described all I could... :(

Answer 3

May be storing in another table UserID,threadID, LastReadDateTime when the user read that thread.

if (LastPostDateTime > LastReadDateTime) you got an unread post.

Sadly you have a great overhead, on every read you'll have a write.

Answer 4

The traditional solution is a join table something along the lines of:

CREATE TABLE topicviews (
    userid INTEGER NOT NULL,
    topicid INTEGER NOT NULL,
    lastread TIMESTAMP NOT NULL,
    PRIMARY KEY (userid, topicid),
    FOREIGN KEY (userid) REFERENCES users(id),
    FOREIGN KEY (topicid) REFERENCES topics(id)
);

with lastread updated every time a topic is read. When displaying the list of topics, if the topics.lastupdated is > topicviews.lastread, there are new posts.

The traditional solution is rubbish and will kill your database! Don't do it!

The first problem is that a write on every topic view will soon bring the database server to its knees on a busy forum, especially on MyISAM tables which only have table-level locks. (Don't use MyISAM tables, use InnoDB for everything except fulltext search).

You can improve this situation a bit by only bothering to write through the lastread time when there are actually new messages being read in the topic. If topic.lastupdated < topicviews.lastread you have nothing to gain by updating the value. Even so, on a heavily-used forum this can be a burden.

The second problem is a combinatorial explosion. One row per user per topic soon adds up: just a thousand users and a thousand topics and you have potentially a million topicview rows to store!

You can improve this situation a bit by limiting the number of topics remembered for each user. For example you could remove any topic from the views table when it gets older than a certain age, and just assume all old topics are 'read'. This generally needs a cleanup task to be done in the background.

Other, less intensive approaches include:

• only storing one lastread time per forum
• only storing one lastvisit time per user across the whole site, which would show as 'new' only things updated since the user's previous visit (session)
• not storing any lastread information at all, but including the last-update time in a topic's URL itself. If the user's browser has seen the topic recently, it will remember the URL and mark it as visited. You can then use CSS to style visited links as 'topics containing no new messages'.

Answer 5

The general ideas here are correct, but they've overlooked some obvious solutions to the scalability issue.

@bobince: The second problem is a combinatorial explosion. One row per user per topic soon adds up: just a thousand users and a thousand topics and you have potentially a million topicview rows to store!

You don't need to store a record in the "topicviews" table if somebody hasn't ever viewed that thread. You'd simply display a topic as having unread posts if null is returned OR of the last_read time is < last_post time. This will reduce that "million" rows by perhaps an order of magnitude.

@gortok: There are plenty of ways to do it, but each grows exponentially larger as the user visits the site.

In this case, you archive a forum after n-posts or n-weeks and, when you lock, you clean up the "topicviews" table.

My first suggestion is obvious and has no downside. My second reduces usability on archived topics, but that's a small price to pay for a speedy forum. Slow forums are just painful to read and post to.

But honestly? You probably won't need to worry about scalability. Even one million rows really isn't all that many.

Answer 6

Bobince has a lot of good suggestions. Another few potential optimizations:

Write the new "is this new?" information to memcached and to a MySQL "ARCHIVE" table. A batch job can update the "real" table.

For each user, keep a "everything read up until $date" flag (for when "mark all read" is clicked).

When something new is posted, clear all the "it's been read" flags -- that keeps the number of "flags" down and the table can just be (topic_id, user_id) -- no timestamps.

Answer 7

You could just use the functionality of the user's browser and append the last postid in the link to the thread like this: "thread.php?id=12&lastpost=122"

By making use of a:visited in your CSS, you can display the posts that the user already read different from those that he did not read.