What is the most efficient way to create a forum lightbulb (unread) system?

Question

Alright, another interesting problem over at Route 50.

We wanted to implement a true forum lightbulb system where posts that are unread by a user (after the user's account is created) show as unread until that status is cleared or until the user reads them.

We figured the best and easiest way to do this would be to implement a table of unread messages.

The Columns are: user_id, board_id, thread_id, post_id, timestamp, and hidden

This is working very well and very quickly for seeing which boards/threads/posts are unread (and linking to them) per user, however it is INCREDIBLY slow for a user to post to the forum even though only a single SQL query is being run:

INSERT IGNORE INTO `forums_lightbulb` SELECT `id`,'x','x','x',UNIX_TIMESTAMP(),0 FROM `users`

I'm sure this is the result of having 3065 user accounts. How can I speed up this process? I'd prefer to keep the system as Real-Time as possible.

Important Note: Please limit your answers to a shared hosting environment with no additional budget. We are limited to PHP and MySQL 5.1.53-log

Answer 1

What PHPBB does is a very quick way to do it. It keeps a table that marks for each thread and each forum when the last time was a user opened it. And uses that to determine if there are unread messages. It allows a Users*Topics + Users*Forums storage usage scheme while allowing a check with pretty simple and fast queries.

You can see how it works from the database structure.

# Table: 'phpbb_forums_track'
CREATE TABLE phpbb_forums_track (
    user_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    mark_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
    PRIMARY KEY (user_id, forum_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

# Table: 'phpbb_topics_track'
CREATE TABLE phpbb_topics_track (
    user_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    mark_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
    PRIMARY KEY (user_id, topic_id),
    KEY topic_id (topic_id),
    KEY forum_id (forum_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

Answer 2

Sorry to say, but the solution proposed in the question is an unscalable design.

I was looking into this problem over here, which had a decent discussion on it (before I showed up). Take a look there.

In your case, storing U*M records to track "unread" posts, where U is the number of users and M is the number of messages, would get out of control very, very quickly. This is because its best-case efficiency requires all users to read every message (and most users don't care about everything, as most everything on a forum is noise). Average case, maybe 20% of users have read 100% of posts, but 80% have read near 0% of posts and never will read the rest. This means that you're being forced to store 0.8*U*M, with U and M only ever increasing, geometrically. No amount of indexing will fix this.

The previous @will-hartung answer has the more efficient approach.

I see that this is pretty old, and I hope you found a better solution in the meantime.