Post reply

Reply to the message by Joe I

Name:

E-mail: (optional, won't be displayed directly)

Leave this field empty:

Homepage: (optional)

Leave this field empty:

Location: (optional)

Remember me (cookie)

Category:

Subject:

Formatting help

skip to input

format text bold: [b]bold text[/b]
format text italic: [i]italic text[/i]
insert hyperlink: [link=http://example.com/]link text[/link] / [link]http://example.com/[/link]
set text color: [color=#rgb]colored text[/color]
font size: [size=small]small text[/size]; [size=large]large text[/size]
insert list: [list][*]list item[/list]
insert image: [img]http://example.com/image.jpg[/img]; left: [img=left]http://example.com/image.jpg[/img]; right: [img=right]http://example.com/image.jpg[/img]; thumbnail: [img=thumbnail]http://example.com/image.jpg[/img]; thumbnail left: [img=thumbnail-left]http://example.com/image.jpg[/img]; thumbnail right: [img=thumbnail-right]http://example.com/image.jpg[/img]
upload image: upload image ...
insert TeX code: [tex]TeX code[/tex]
insert code: [inlinecode]code[/inlinecode]; [code]code[/code]; [code=css]code[/code]; [code=html]code[/code]; [code=javascript]code[/code]; [code=perl]code[/code]; [code=php]code[/code]; [code=sql]code[/code]; [code=xml]code[/code]

:-)
;-)
:-P
:-D
:-|
:-(
:yes:
:no:
:ok:
:lol:
:lol2:
:lol3:
:cool:
:surprised:
:angry:
:crying:
:waving:
:confused:
:lookaround:
:clap:
:love:
:tick:

Message:

> 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:
> 
> [b]Base Code:[/b]
> 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).
> 
> [b]Modified Code:[/b]
> 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 [b]rst.user_id[/b] = 0
> Modified:
> 	LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id AND [b]IFNULL(rst.user_id, NULL)[/b] = 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

E-mail notification on reply of this posting