Hello Joe

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
base

-- total_spam base (650 ms)
-- main.inc.php
SELECT COUNT(*) FROM mlf2_entries 
LEFT JOIN mlf2_akismet_rating ON mlf2_akismet_rating.eid = mlf2_entries.id 
LEFT JOIN mlf2_b8_rating ON mlf2_b8_rating.eid = mlf2_entries.id 
WHERE (mlf2_akismet_rating.spam = 1 OR mlf2_b8_rating.spam = 1)

mod

-- total_spam Mod 2 (400 ms)
SELECT COUNT(*) FROM mlf2_entries 
LEFT JOIN 
(SELECT eid AS ak_eid, spam AS ak_spam FROM mlf2_akismet_rating WHERE spam = 1) AS akismet 
ON ak_eid = mlf2_entries.id
LEFT JOIN 
(SELECT eid AS b8_eid, spam AS b8_spam FROM mlf2_b8_rating WHERE spam = 1) AS b8 
ON b8_eid = mlf2_entries.id 
WHERE ak_spam = 1 OR b8_spam = 1

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.

total_postings
base

-- total_postings (no spam) - base [1 s]
-- main.inc.php
SELECT COUNT(*) 
FROM mlf2_entries 
LEFT JOIN mlf2_akismet_rating ON mlf2_akismet_rating.eid = mlf2_entries.id 
LEFT JOIN mlf2_b8_rating ON mlf2_b8_rating.eid = mlf2_entries.id 
WHERE (mlf2_akismet_rating.spam = 0 AND mlf2_b8_rating.spam = 0)

mod

 
-- total_postings (no spam) - Mod 7 [650 ms] 
-- Faster options require logic rewrite to display_spam_query_where and display+spam_query_and
-- main.inc.php
SELECT STRAIGHT_JOIN COUNT(*) 
FROM mlf2_entries 
LEFT JOIN mlf2_akismet_rating ON mlf2_akismet_rating.eid = mlf2_entries.id 
LEFT JOIN mlf2_b8_rating ON mlf2_b8_rating.eid = mlf2_entries.id 
WHERE (mlf2_akismet_rating.spam = 0 AND mlf2_b8_rating.spam = 0)

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