Read/Unread Thread System

[robgolding63]

Hi,

I have written a forum system in php, trying to keep it as scalable as I can. I have just finished optimizing every feature, apart from the read/unread thread feature. Basically, as most other forums have, a different icon displayed next to the forum/thread if there has been a post since the user last visited the topic.

Currently, the system works by storing comma-seperated values in a field in the `topics` table, called "viewed_by". This field stored the UserID's of people who have read the topic, and every time the topic is updated, it is purged.

My question is, how can i redesign this feature to be more efficient. As is stands currently, this is slowing the board down as the system has to traul through thousands of records (exploding each one into an array, then searching it for the user's ID), just to ascertain whether the user has an unread topic under a particular forum. And it does this for every forum, of which there are 38!

I suppose this might be more related to MySQL, as I am totally willing to completely overhaul the whole system, and add new table to the database if it would help, but I just can't figure out what I could do to make it more efficient.

Any help would be greatly appriciated,

Rob

[shocker-z]

Well you could store in the users name but then you may end in the same situation.. Possibly an extra table with the threadID and user ID and then when you select all the threads you lookup that threadID in the table and then check userID in the same row? therefore your linking and going through alot less records as what's the moist views for 1 thread likley to be? probs 100max?


Regards
Liam

[robgolding63]

As I said before, I am really trying to make it as scalable as I possibly can. I am writing this with the assumption that thousands of users will be viewing threads like crazy! Good idea though, I'll look into how i can utilize a seperate table for this.

Regards,

Rob

[Zane]

I've always wondered how that feature would work.  It baffles me everytime.
But the idea I come up with everytime at the end is this.

You would want it so that you can tell if they've seen the post or not...not the thread.
cuz if you think about it.  Everytime someone posts a new reply...that's a new post you haven't seen.  And once you view the thread again you have.

It makes it that much harder though.  I'd say have an extra column in the users table for posts read and continue to populate a serialized array you have in there...maybe at a fulltext datatype.

Just throwing an idea out there not sure of it's quality
Hope I inspired something

[shocker-z]

that way wouldn't work because that means when a new post is added you would have to loop through every user in your database and delete that thread ID! the way i said you could just wipe the column of names blank next to the threadID everytime a user posts a reply.

I can't think of a way to get it any better than that as use using 2 columns and surely using the minimum bandwidth this way?

Liam

[robgolding63]

Yeah Liam's way would mean LESS in the column dedicated to this feature, but it's still using the same method. zanus, I know what you're saying here, I'm completely confused by how the big forums do this. I'm sitting here, with phpBB's viewforum.php in front of me, and i can't for the life of me work out how this works:
[code]
if( !empty($tracking_topics) || !empty($tracking_forums) || isset($HTTP_COOKIE_VARS[$board_config['cookie_name'] . '_f_all']) )
{
$unread_topics = true;
                                        }
[/code]

This is getting more and more complicated!

Thanks for the help anyway guys.

Regards,

Rob