Hello Joe

I'm now done with my code changes and extensive testing of the SQL performance modifications. Since it does not appear that I can write to My-Little-Forum, I created a fork and pull request for the changes.

That's the normal way, even for us team members (partially). Also we must provide our changes with a pull request. I do this, for historic reasons, from my fork, Micha typically with a pull request from a branch in the original repository (because he – as a team member – has access to the original repository).

4 total includes files changed: entry, index, main, and thread.

Until now I did not dive into your code in the PR. The list of changed files tells me, that these are changes in the operation code of a running forum and no changes in the upgrade script. So there shouldn't be interferences between these two "sections".

Performance Note: In all instances tested, page load time went from 10-15 seconds in base 20240308.1 down to sub 1 second with these modifications.

😀👌

That's a very remarkable enhancement.

I do see about a 40% increase in page load time over 2.4.24 (from 500ms to 800ms avg), but page load is still acceptably under 1 second, even within our forum of 500,000 posts / 40,000 threads.

With increasing complexity of the database queries, a certain slowdown is to be expected. However, your measurements remain within the limits of what is initially acceptable.

If these modifications work for you, it should hopefully eliminate a need to further customize SQL queries based on forum spam settings.

At the very least, it would make it for a start unnecessary to split the queries according to registered and non-registered users, according to whether spam protection measures are switched on or not or according to other unnamed criteria.

If and when we see the necessity to split queries I would like to do this in stored procedures on the MySQL-server but not in the PHP-code. To do this, we need a reliable way of recognising whether the database user of a forum operator is allowed to create, change, delete and execute stored procedures. If not, an upgrade to the corresponding version must be prevented. With the complete conversion of the database engine to InnoDB, we have at least laid the foundation for this because we can now work with transactions which is a precondition to reliably create stored procedures with the mysqli-library (as far as I see in code examples).

Tschö, Auge