Unread posts and slow queries

[cookdandbombd]

I've been working a lot on optimising my forum and am finding the "unread topics" feature is showing up a lot in the mysql slow query log. I do have load balancing turned on to shut it down when needed.

Rows_examined: 677359

Does the query have to do all that work? Is there any way to limit it?

SMF 2.1.4

[shawnb61]

Not a lot of details to work on here, but I suspect the "Mark Boards as Read" scheduled task may help. 

It is not enabled by default, because it deletes a lot of old (likely unused) information.

It can be a LOT of rows...  SMF keeps track of what everyone has read & what they haven't read - all the way down to the board & topic level.  So, yeah, that can be a LOT of data...  Close to every user * every board...  The "Mark Boards as Read" scheduled task mimics folks going in & marking their boards as read, which cleans up a surprising number of records.

And further, for uses who haven't been around in a while, like a year or more, face it, everything is new to them.  So the "Mark Boards as Read" function just deletes all that read/unread history for those folks.  Why keep any of that detail around for them...  When they return, everything will appear unread.

Timeframes can be adjusted.  Look at the "Read Logs" section under Admin | Maintenance | Logs | Log Settings.

If you have a lot of cleanup to do, you may need to increase the # of users to process at a time & run it manually a bunch of times.  There can easily be millions of rows of data to clean.

[cookdandbombd]

Thank you so much!  That's phenomenal, I'll get cracking on that now. 

While I'm here, thanks also for the brilliant info you've given about blocking bots in other threads, it's been an enormous help.