
20220803.1 performance comments, first questions (General)
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 = 1The 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 JOIN
s. Does this not contradict itself?
Tschö, Auge
--
Trenne niemals Müll, denn er hat nur eine Silbe!