Need advice for creating "unread" on forums

[therehere3]

Hi there! I have created a forums system on my website using PHP and MySQL.

However, I would like to show a red unread icon showing the user that is logged in that they have not read this thread. 

The only way I can think of doing this would be to simply create a table with the user id and thread id when they view a thread so if it is not in this table, then simply show the red icon.

However.... I see this being a problem since this table would grow INCREDIBLY long and INCREDIBLY fast.

There has to be a better solution right? Any advice would help, thanks!  

[requinix]

3 minutes ago, therehere3 said:

However, I would like to show a red unread icon showing the user that is logged in that they have not read this thread.

It'll be a lot more useful if you can track not just whether they've read the thread but whether there have been any new posts since then. Because if I read a thread and someone adds to it later, now I haven't read the thread anymore.

8 minutes ago, therehere3 said:

However.... I see this being a problem since this table would grow INCREDIBLY long and INCREDIBLY fast.

Grow long? Yes. Grow relatively fast? Yes.
Be a problem? No. Database systems are designed for this sort of thing, and with good design on your part (especially indexes) a large table won't be any problem at all. At least not for mortals like you and me - grow to Facebook scale and we'll have to revisit this.

You can also consider a "mark all as read" feature that will help here. You store a timestamp with the user, that applies to either the entire site or to just one subforum, that tracks the last time they hit the "mark all as read". Checking if a thread has been unread or not is a two-step process of checking (1) whether the most recent timestamp for it is after the mark-as-read timestamp then (2) the thread's most recent post is not recorded in the has-previously-read table. Which means that if/when the user hits the button you can also zero-out their records in the read table.

[therehere3]

6 minutes ago, requinix said:

It'll be a lot more useful if you can track not just whether they've read the thread but whether there have been any new posts since then. Because if I read a thread and someone adds to it later, now I haven't read the thread anymore.

Grow long? Yes. Grow relatively fast? Yes.
Be a problem? No. Database systems are designed for this sort of thing, and with good design on your part (especially indexes) a large table won't be any problem at all. At least not for mortals like you and me - grow to Facebook scale and we'll have to revisit this.

You can also consider a "mark all as read" feature that will help here. You store a timestamp with the user, that applies to either the entire site or to just one subforum, that tracks the last time they hit the "mark all as read". Checking if a thread has been unread or not is a two-step process of checking (1) whether the most recent timestamp for it is after the mark-as-read timestamp then (2) the thread's most recent post is not recorded in the has-previously-read table. Which means that if/when the user hits the button you can also zero-out their records in the read table.

First, thank you, I really appreciate the reply!

Second, I like this "mark all read" solution as well. However, if I do go down the right of keep entries of a user id read thread id, would I still want to have a combined index on my DB of userid+threadid even though each insert will require a re-index. But if I don't use that as my index it will be creating a full table search correct? I don't think there is any other way around creating that index for it even though inserts will be slower. Would love to hear your thoughts. 

[requinix]

55 minutes ago, therehere3 said:

Second, I like this "mark all read" solution as well. However, if I do go down the right of keep entries of a user id read thread id, would I still want to have a combined index on my DB of userid+threadid even though each insert will require a re-index. But if I don't use that as my index it will be creating a full table search correct? I don't think there is any other way around creating that index for it even though inserts will be slower. Would love to hear your thoughts. 

Correct.

Consider how you'll be using this table and how often. When will you need to read it? When will you need to write to it?

Writing is infrequent: when a user views a thread. Humans don't do that at any kind of speed a computer might consider "fast". Open a few tabs at a couple per second, then stop while they read. That's nothing.
But reading, that's a lot more frequent. You'll need it for thread listings (what's read and what isn't), probably for thread viewing (mark where "new" content starts), perhaps for daily tasks. Still not exactly a large volume, but the point is it's much more frequent than reading.

So the conclusion? Go ahead and sacrifice some write performance if it gains you read performance.

I'm thinking

CREATE TABLE whatever (
	id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
	user_id bigint unsigned NOT NULL,
	thread_id bigint unsigned NOT NULL,
	last_read DATETIME NOT NULL,
	UNIQUE (user_id, thread_id)
) ENGINE=MyISAM;

 

[kicken]

2 hours ago, therehere3 said:

even though each insert will require a re-index

For what it's worth, and maybe it'll help with your conception of this being a "problem", the database won't do some complex re-index operation with each insert.  The database maintains the index in a particular sorted structure and would just insert the new row data into that structure where it needs to go. 

I have a system setup that is pretty much exactly as requinix is describing.  When a user visits a particular discussion I insert a row recording the time they visited that discussion.  Table is simple, just the discussion id, login id, and the time with an index across the three columns. 

CREATE TABLE discussion_view_history (
	DiscussionId int NOT NULL,
	LoginId int NOT NULL,
	ViewedOn datetime NOT NULL,
    index IX_last_viewed (DiscussionId, LoginId, ViewedOn)
)

To obtain a list of discussions with a count of total and unread posts, the query looks a little something like this.

select 
    d.Id
    , d.Title
    , COUNT(p.Id) as totalPosts
    , sum(case when vh.lastViewedOn is null or p.PostedOn > vh.lastViewedOn then 1 else 0 end) as totalUnread
from discussion d
inner join forum f on f.DiscussionId=d.Id
inner join discussion_post p on p.DiscussionId=d.Id
left join (
    select DiscussionId, LoginId, MAX(ViewedOn) as lastViewedOn
    from discussion_view_history
    group by DiscussionId, LoginId
) vh on vh.DiscussionId=d.Id and vh.LoginId=$viewerLoginId
where
    f.Id=$forumId
group by
    d.Id, d.Title

 

2 hours ago, therehere3 said:

However.... I see this being a problem since this table would grow INCREDIBLY long and INCREDIBLY fast.

For some context that might help alleviate that fear as well, my discussion table has 250,050 rows, discussion_view_history table has 2,104,958 rows, and discussion_post table has 4,226,291 rows and that query returns results in 44 milliseconds.

Focus on making well structured tables with appropriate indexes and good queries.  Don't worry about how big your tables or indexes might end up being.

 

Edited September 18, 2022 by kicken