First questions. More will follow.

My mods to 20220803.1 are below (note, these are the queries as run through SQL testing. I can post the actual php code changes if desired. The php code mods should have a net zero effect on all board installations, regardless of configuration.

total_spam

The differences between these two are:

• akismet and b8 JOINs are moved into subqueries

It's looking plausible to use pre filtered results for joining. Additionally this removes the OR in the WHERE-clause, that alone can lead to a full table scan instead of using an existing index, from the where clause.

Yes, agreed on pre filtering and table scan issues. In this particular case, the above changes can be made without any pre filtering and the performance difference is quite significant.

total_postings

The differences between these two are:

• Add STRAIGHT_JOIN to force order of JOINs

Here I have a serious question. This is the first time I read about STRAIGHT_JOIN. But allexamples I can find are using it in the FROM … JOIN … section but not directly after SELECT. Additionally the MySQL documentation states STRAIGHT_JOIN as a replacement for pure JOIN (which itself is a shorthand for INNER JOIN). We are using LEFT JOINs. Does this not contradict itself?

Tschö, Auge

Ahh, good question. I will caveat this first by noting that I am running on MariaDB. MySQL has an additional optimizer hint (/*+ JOIN_FIXED_ORDER */) that will also work in forcing the order of the JOINs, but MariaDB has not (yet) implemented this hint. Fort compatibility across both, I'm using STRAIGHT_JOIN.

Both MySQL and MariaDB allow for use of the STRAIGHT_JOIN in either the SELECT or FROM clauses.

You are correct in that using the STRAIGHT_JOIN in the FROM clause will act as an INNER JOIN.

In MariaDB (and my untested understanding for MySQL), using the STRAIGHT_JOIN in the SELECT will simply force the order of the JOINs as specified in the FROM clause, but will not force INNER JOIN for tables in the FROM clause. I have confirmed this to be true in MariaDB, and the LEFT JOINs still work properly.

Note: I am still working on performance improvements to 20220803.1. It's not yet fast enough for us to roll out on our forum, so I'm sticking with my modded 2.4.24 for now. Performant JOINs on the akismet_rating and b8_rating tables are still eluding me, but I'll keep trying.