« Project home page
my little forum
Log in
Register
Search:
Back to the entry by Joe I
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:
General
Project organisation
Technics
Design/Themes
Features
Development
Todo
Bugs
German / Deutsch
Spanish / Español
French / Français
Accessibility/UX
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:
> Hi Micha, > > > > 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. > > > > This looks strange. In the first statement, [inlinecode]rst.user_id[/inlinecode] is compared with zero. It is like > > > > [code=php]if (rst.user_id == 0) { > > //do something > > }[/code] > > > > In your modified statement, a second condition is added in front of the prior condition. However, the second one is [i]identical[/i] to the condition of the first statement. > > [code=php]if (rst.user_id == NULL) { > > rst.user_id = NULL; > > } > > if (rst.user_id == 0) { > > //do something > > }[/code] > > > > I could have immediately understood that the following change would make an improvement (no further type-cast), > > > > [code=php]if (rst.user_id == NULL) { > > rst.user_id = -1; > > }[/code] > > Yes to most of the above, and yes it's strange. > > For clarity, the 0 in "AND [b]rst.user_id[/b] = 0" refers to the logged in User ID. > The actual code in index.inc.php is AND rst.user_id = ". intval($tmp_user_id) ." > If there is no logged in user, then tmp_user_id is set to 0, and the query would be "AND rst.user_id = 0" > If Admin is logged in, the query would be "AND rst.user_id = 1" > > For our DB: > In any instance where the specified user has a record in mlf2_read_entries, the result is immediate. > In any instance where the specified user does not have a record, the response is quite slow. > > NOTE: From some quick testing on my local machine, there appears to be a difference in the way MySQL 8 and MariaDB 10 handle this. > MySQL appears to run this query quickly regadless. > MariaDB gives the performances differences noted above. > > On MariaDB, I did confirm material query plan differences when using using IFNULL and without, for different User IDs, and that is the underlying issue. IFNULL is a workaround to make MariaDB act smartly. > > Given the above notes, YMMV based on installation type. > > > > > However, if such a simple change works, we should change the statements... > > > > /Micha > > For someone still running 2.4.24 and having performance issues, I think this would be a very easy change, and potentially very helpful. > It may also be helpful for 20220803.1, so let me check into that as well and post back here. > > > Joe
E-mail notification on reply of this posting
OK - Submit
Preview