2.4.24 performance comments. (General)
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).
OK, good and yes.
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.
Testing on my board indicates that the simple change of wrapping rst.user_id inside an IFNULL (for the latest_postings query) should work well across all cases.
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.
Agree on stored procedures overall.
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.
see above; I don't even think that separate queries are necessary in this instance.
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_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).
Yes, understood. At many points in development you have to weight the complexity / performance tradeoff.
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.
Understood. That was more a mental note that the DB in use can affect performance on top of so many other variables.
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.
You're quite welcome and see my 20220803.1 notes I'm about to post.