创建论坛灯泡（未读）系统的最有效方法是什么？

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`;