2.4.24 performance comments. (General)
I dug into the issue I encountered within 2.4.24 where home page performance was much slower for non logged-in users versus logged in. That assessment was close, but not complete. The performance issue relates to whether a given user has any records in the mlf2_read_entries table or not, and specifically to the query(ies) which retrieve last_postings. [Note: our forum ~500,000 entries.]
Following is the code to retrieve last_postings, with $categories == false. There are different queries based on forum categories being active and containing records, but the structure is similar. This is in /include/index_inc.php:
Base Code:
SELECT id, pid, tid, name, user_name, ft.user_id, UNIX_TIMESTAMP(ft.time) AS time, UNIX_TIMESTAMP(ft.time + INTERVAL 0 MINUTE) AS timestamp, UNIX_TIMESTAMP(last_reply) AS last_reply, subject, category, rst.user_id AS req_user
FROM mlf2_entries AS ft
LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id = ft.user_id
LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id AND rst.user_id = 0
WHERE spam = 0
ORDER BY ft.time DESC LIMIT 1;
When running this on our forum for users who have mlf2_read_entries records, it runs <0.25 seconds.
When running this on our forum for users who do not have mlf2_read_entries or are not logged in, it runs over 3 seconds.
The issue here stems from performing a LEFT JOIN on a table that returns no results. I am not sure if it's because this query decided to use a table scan instead of an index, but it's significant. I made a simple but obscure tweak to the query, and performance is now quick in all instances. We'll stick with the modified 2.4.24 until I can get better performance out of 20220803.1 (or later).
Modified Code:
SELECT id, pid, tid, name, user_name, ft.user_id, UNIX_TIMESTAMP(ft.time) AS time, UNIX_TIMESTAMP(ft.time + INTERVAL 0 MINUTE) AS timestamp, UNIX_TIMESTAMP(last_reply) AS last_reply, subject, category, rst.user_id AS req_user
FROM mlf2_entries AS ft
LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id = ft.user_id
LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id AND IFNULL(rst.user_id, NULL) = 0
WHERE spam = 0
ORDER BY ft.time DESC LIMIT 1;
The only difference is on this line, and I've bolded the change:
Base:
LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id AND rst.user_id = 0
Modified:
LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id AND IFNULL(rst.user_id, NULL) = 0
I've come across this before and it always seems bizarre, but it works.
FYI my background is also in MS SQL, and thanks for your continued work on this.
Joe