2.4.24 performance comments. (General)
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) = 0I'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 likeif (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