Forum Lightbulb (Read/Unread Posts) System (Taking too long to Post)

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.

Answer 1

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.