How to make an unread topics list with mysql

Question

What is an efficient way to display a list with unread posts in a forum? It is possible to track the user and logging each post he or she visits and registering it in a new table.

I think this is not very efficient. What is a more efficient way to do this?

Answer 1

If you really want to do this, you could have a table as

UserVisit: user_id, topic_id, last_visited_at

which table you update whenever a user opens a topic. Then you can select the topics of which the id is not in this table, or which has a post added later than last_visited_at. Something like:

SELECT *
FROM Topic
WHERE Topic.id NOT IN (SELECT topic_id FROM UserVisit WHERE user_id = $user_id)

UNION

SELECT *
FROM Topic
LEFT JOIN UserVisit ON Topic.id = UserVisit.topic_id
WHERE user_id = $user_id
AND UserVisit.last_visited_at < Topic.last_post_at

But I would suggest that instead of this, you only present the user with the topic that have new posts since the user's last visit to the site. If you do this, you don't need this table at all, you can query topics as:

SELECT *
FROM Topic
WHERE Topic.last_post_at > $users_last_visit