2.4.24 performance comments. (General)

by Joe I, Thursday, April 06, 2023, 16:43 (375 days ago) @ Joe I
edited by Joe I, Thursday, April 06, 2023, 17:06

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


Complete thread:

 RSS Feed of thread