Maintenance tool to handle unread posts.

Topic: Maintenance tool to handle unread posts. (Read 2626 times) previous topic - next topic

0 Members and 1 Guest are viewing this topic.

Maintenance tool to handle unread posts.

March 04, 2014, 05:27:18 pm

Been thinking about the unread flags in the db. These can build up to quite insane levels on established fora that have large numbers of posts and large numbers of inactive accounts.

There's an admin option to delete inactive accounts, but you don't always want to do that. So, I've been thinking that the maintenance section (or maybe the scheduled tasks section) could do with an option to mark all posts as read for inactive accounts, with inactive being defined as whatever the admin thinks is suitable.

I suggested this to Eliana, and she thought it might be better to do it for inactive topics rather than inactive accounts. That makes sense too. Which approach will be better depends the number of inactive accounts and existing topics on the forum in question. The only catch I can see with doing it by topic is stickies, which are often inactive for long periods, but that could be handled by having an exemption for stickies.

So, anyone think this tool is worth having?

Re: Maintenance tool to handle unread posts.

Reply #1 – March 04, 2014, 05:33:58 pm

Rationale for doing it by posts/topics rather than users:

Quote(4:13:09 PM) Eliana Tamerin: instead of doing it by user, why not do it by post?
(4:13:40 PM) Eliana Tamerin: if you have a big board with 2 million posts and 20 users, that's potentially 40 million rows in your table
(4:14:28 PM) Eliana Tamerin: even if you mark all as read for inactive users (let's say that's 10 of your users), the next time someone joins, the table is still going to grow by 2 million rows

Re: Maintenance tool to handle unread posts.

Reply #2 – March 04, 2014, 05:39:41 pm

Just a quick random thought while reading: now in log_topics are stored the unwatch statuses as well, so cut down that table could be a bit more tricky then in the past.
Probably still worth doing.

Re: Maintenance tool to handle unread posts.

Reply #3 – March 05, 2014, 11:27:49 am

There are 3 tables involved and its a bit of a shell game as I recall. I think this is generally how the tables are done

log_topics = If you go in to a topic you get an entry per topic here so the system can keep track of your position. So this is one entry per topic per member IF they have gone to that topic.
log_mark_read = If you have marked a board as read, this is the highest message number (at that time) for that board (and therefore all topics in that board). This is one entry per board per member.
log_boards = Current highest message number for each board (that the member has ever gone in to), same structure as log_mark_read, one entry per member per board. Compare this and the mark read to see if there are new messages

A new member does not get a row per topic for every topic on the site in the log_topics table, they only get entries for topics they have specifically read.

The mark read for inactive has the most benefit (typically) for sites with a lot of users that are or were active, ones that read 70/80% of a topic and then moved on. For those it shifts many entries from log_topics to single entries in log_mark_read and log_boards, allowing deleting of the old rows in the log_topics table Overall thats a savings (less rows)

On the other had if you have a lot who joined and left, you will not have many entries in the log topics table and when you run the maintenance you will end up with more entries in the log_mark_rad and log_boards tables then there were in the log_topics table to begin with, overall negative in terms of number of total rows in the database across those 3 tables. So they joined to read a topic, and left .. say you have 20 boards, run the script and you remove 1 entry and add 40.

Whats best for overall performance I'm honestly not sure.

Re: Maintenance tool to handle unread posts.

Reply #4 – March 05, 2014, 02:44:16 pm

It would have to be messy. I'm getting a picture of why SMF just has "delete inactive accounts".

I suppose the only way of finding out which one woud be better would be to compare the stats from actual databases from established fora. You can't tell from the admin gui, since that only lists post counts, etc.

Re: Maintenance tool to handle unread posts.

Reply #5 – March 05, 2014, 03:48:03 pm

I guess there may be a way to check how many entries a user has in the topics table ...

Say if its less than some threshold, leave it alone, threshold being something like 4X the number of boards. If however they are above said threshold, then you can move the entries from log_topics to the other two tables. That should help prevent ballooning up the size of the log_mark, log_board tables for the members that joined to read a couple of topics and have never been seen again.

Then there is the item that emanuele mentioned, not sure how to handle that one either.

Re: Maintenance tool to handle unread posts.

Reply #6 – March 05, 2014, 07:54:51 pm

I think a good plugin would be to have deleting a post (recycling) remove all read/notification logs. Would be even more powerful if you could make that a board option. Every board could have the option to have read/notification logs disabled. So if you have an "archived" board, it would just delete all of the logs and then not show any new icons and not show up in the unread actions.

Instead of deleting old accounts, you could just remove all of the logs for those users.

Another way would be to use their last_visited date as a date of when they would be marked read. Then, when they return everything before their last visit will be unread. You'd still remove all of the logs. That seems like a much more complicated case.

I think the only way that you could figure out what is the better option - leaving the log_topics or using the log_mark_read - is to count. Compare count(log_topics) with a threshold (1?) and then mark those boards for those members. Something like (sorry if it doesn't work or is weird, took like 2 minutes to write and I didn't check the tables for the correct columns)

Code: [Select]

SELECT lt.id_member, t.id_board, COUNT(lt.*) as lt_count
FROM members m
INNER JOIN log_topics lt USING(id_member)
INNER JOIN topics t USING(id_topic)
WHERE m.last_visited < NOW() - (60 * 60 * 24 * 90)
GROUP BY m.id_member, t.id_board
HAVING lt_count > 1

You could also just split out the member part and use "id_member IN({array_int:members})"