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

Unbeknown

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

Fix

This might be what I'm looking for! Thank you! I'll award the bounty unless someone comes along with a better answer, but I'm pretty sure this is what I was trying to figure out! – Unbeknown

That's what open source software is for right? Learning from each other :D – Fix

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.

Mulcahy

I theorized a better solution, which is a mixture of timestamps and storing messages read, or something like that. I haven't completely planned it out. In the meantime, though, I just disabled the lightbulb system. – Unbeknown

Answer 3

Here is the most efficient way:

1. have a table called read_threads which stores the thread_id, and user_id
2. have a column in the users table called mark_read_date which stores the date the user clicked on the mark all threads read link in your forum
3. in order to determine if a thread is read, your query will check if it is in the read_threads table, or if its last_post_date (the date the last post was made to it) is older than the users mark_read_date

It is important that you also remove all rows from the read_threads table when a user clicks the mark all threads read link in your forum.

Isobath

Answer 4

On read:

insert into read_articles(user_id, article_id);

On display:

SELECT a.*, r.user_id FROM articles a 
LEFT OUTER JOIN read_articles r ON (a.article_id = r.article_id and r.user_id = $user_id)
WHERE (article_filter, like forum or thread id, or whatever)

On your result set, if user_id is not null, then they've read the article. Otherwise, they haven't.

Index as appropriate. Server warm with biscuits and jam.

Brownfield