Hello

For clarity, the 0 in "AND rst.user_id = 0" refers to the logged in User ID.
The actual code in index.inc.php is AND rst.user_id = ". intval($tmp_user_id) ."
If there is no logged in user, then tmp_user_id is set to 0, and the query would be "AND rst.user_id = 0"
If Admin is logged in, the query would be "AND rst.user_id = 1"

For our DB:
In any instance where the specified user has a record in mlf2_read_entries, the result is immediate.
In any instance where the specified user does not have a record, the response is quite slow.

Ok, now your and Michas discussion becomes more clear to me. For the read state we can assume the following preconditions.

1. Only registered users can have records about read entries. This is a feature for registered users only.
2. Marking entries as read in the thread trees and the latest entries listing makes no sense for not logged in users (registered or not).

It would be optimal to have two queries at this point, one for logged in users ($tmp_user_id > 0) and one for all others. Our problem is, that there are already several queries with several additions for optional conditions (categories, etc.). It is already complicated to read the code, such a change would make it more complicated.

Stored procedures would be a nice replacement for the complex evaluation of conditions on the side of PHP and would lower the complexity because of the separation of the SQL code from the PHP scripts. As shown a few entries aside it would be possible to have clearly arranged SQL code that can cover several (sub) cases in one procedure that would be called from the PHP script in one or a few lines of code.

If and when we decide and implement the stored procedures, we are at the point to say, from version xy on we only support installations, where the MySQL- or MariaDB-server supports the database user to create, alter and execute the stored procedures.

For now it would be enough to check the cases and to separate the query strings.

A similar although not identical case is the spam check status. If a forum is a fresh installation of a version of the 2.5 branch (20220508.1 or newer), there will be entries in the spam check tables (mlf2_b8_rating and mlf2_akismet_rating) for every created entry. In upgraded forums the update script should create those spam check entries for all existing forum entries. As a result there should be no forum entry without the corresponding spam check entries. But now we join the spam check tables regardless of the actual settings for checking forum entries with B8 and/or Akismet (or not).

With separating the different cases the queries could be simpler depending on the actual settings. That makes it not unnecessary to check, why the queries are so slow, at least for admins and mods (all reports was made by admins/mods/forum operators).

NOTE: From some quick testing on my local machine, there appears to be a difference in the way MySQL 8 and MariaDB 10 handle this.
MySQL appears to run this query quickly regadless.
MariaDB gives the performances differences noted above.

Beside from getting the code generally so fast as it is possible, we have no influence on the existing software equipment.

For someone still running 2.4.24 and having performance issues, I think this would be a very easy change, and potentially very helpful.
It may also be helpful for 20220803.1, so let me check into that as well and post back here.

Thank you for your effort.

Tschö, Auge