2.4.24 performance comments. (General)

by Joe I, Wednesday, April 12, 2023, 22:11 (351 days ago) @ Micha

Hi Micha,

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.


This looks strange. In the first statement, rst.user_id is compared with zero. It is like

if (rst.user_id == 0) {
//do something
}

In your modified statement, a second condition is added in front of the prior condition. However, the second one is identical to the condition of the first statement.

if (rst.user_id == NULL) {
rst.user_id = NULL;
}
if (rst.user_id == 0) {
//do something
}

I could have immediately understood that the following change would make an improvement (no further type-cast),

if (rst.user_id == NULL) {
rst.user_id = -1;
}

Yes to most of the above, and yes it's strange.

For clarity, the 0 in "AND rst.user_id = 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


Complete thread:

 RSS Feed of thread