Fixed Inefficient query in XF\ReactionAbstractHandler::updateRecentCacheForUserChange

Kirby

Well-known member
Affected version
2.2.13
PHP:
\XF::db()->query("
    UPDATE (
        SELECT content_id FROM xf_reaction_content
        WHERE content_type = ?
        AND reaction_user_id = ?
    ) AS temp
    INNER JOIN {$table} AS reaction_table ON (reaction_table.`$primaryKey` = temp.content_id)
    SET reaction_table.`{$recentField}` = REPLACE(reaction_table.`{$recentField}`, ?, ?)
", [$this->contentType, $newUserId, $oldFind, $newReplace]);

This query tries to update all records for $newUserId, even those whre nothing needs to be changed.

In case $newUserId is 0 this could hit a lot of unaffected records (if there are already many reactions by deleted users).
See related issue https://xenforo.com/community/threa...ce-when-renaming-a-user-upon-deletion.217244/

Changing this to smth. like
PHP:
\XF::db()->query("
    UPDATE (
        SELECT content_id FROM xf_reaction_content
        WHERE content_type = ?
        AND reaction_user_id = ?
    ) AS temp
    INNER JOIN {$table} AS reaction_table ON (reaction_table.`$primaryKey` = temp.content_id)
    SET reaction_table.`{$recentField}` = REPLACE(reaction_table.`{$recentField}`, ?, ?)
    WHERE reaction_table.`{$recentField}` LIKE ?
", [$this->contentType, $newUserId, $oldFind, $newReplace, '%' . \XF::db()->escapeLike($oldFind) . '%']);

seems to significantly speed up the query in those cases.
 
Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.2.14).

Change log:
Use a more efficient query when updating reaction caches for content
There may be a delay before changes are rolled out to the XenForo Community.
 
Back
Top Bottom