Fixed Extremely inefficient reaction rebuild for deleted users

[mattrogowski]

Affected version

2.2, 2.3

We have a xf_reaction_content table with approching 200 million rows, and there seems to be an issue when reactions are rebuilt during user deletions.

The query in XF\Reaction\AbstractHandler::updateContentReactions which runs the sub-query SELECT content_id FROM xf_reaction_content WHERE content_type = ? AND reaction_user_id = ? is running it with a user ID of 0 - but there's just over 6 million rows with a user ID of 0. As this grows, this query seems to be getting worse and worse.

Here's some Claude output:

Issue: XF\Service\User\ContentChange::stepRebuildReactions() triggers an unbounded, ever-growing slow query on every user deletion, via XF\Reaction\AbstractHandler::updateRecentCacheForUserChange().

Table involved: xf_reaction_content — 191M rows on our production install.

The query that's slow:

SELECT content_id FROM xf_reaction_content
WHERE content_type = 'xfmg_media'
AND reaction_user_id = '0';

Root cause

The step order in ContentChange::$steps is:

stepReassignContent
...
stepRebuildLikes
stepRebuildReactions

stepReassignContent() runs first and does:

UPDATE IGNORE xf_reaction_content SET reaction_user_id = 0 WHERE reaction_user_id = <deletedUserId>

(via setupForDelete(), which sets $newUserId = 0).

By the time stepRebuildReactions() runs afterward, the specific rows belonging to the just-deleted user are no longer distinguishable in the table — they've already been folded into reaction_user_id = 0, which is the shared placeholder that every deleted user's reactions get funneled into, forever.

So updateRecentCacheForUserChange($oldUserId, 0, ...) is forced to run:

SELECT content_id FROM xf_reaction_content
WHERE content_type = ?
AND reaction_user_id = 0

which isn't "content this user reacted to" — it's "content any deleted user, ever, has reacted to." That set only grows as more accounts are deleted over the life of the site, so this query gets slower every time any user is deleted, indefinitely.

This is different from the merge path (setupForMerge()), where $newUserId is a specific real account, so the equivalent query is naturally bounded to that one user's reaction history.

Why it can't be fixed after the fact

stepReassignContent() overwrites reaction_user_id to 0 before stepRebuildReactions() runs, destroying the only signal that would let the rebuild step scope itself to just this deletion's own content. The recent-reactors cache still legitimately needs correcting (it holds the old user_id/username and shouldn't point at an id that could later be reused by a new registration) — but the current implementation rediscovers the affected content from an unbounded shared bucket instead of a bounded, deletion-specific set.

Suggested fix

Capture the affected content_ids before stepReassignContent overwrites reaction_user_id — e.g. snapshot:

SELECT content_id FROM xf_reaction_content WHERE content_type = ? AND reaction_user_id = <originalUserId>

prior to the reassignment, and pass that bounded id set into stepRebuildReactions/updateRecentCacheForUserChange, instead of re-deriving it afterward from the shared reaction_user_id = 0 bucket.

Also worth noting: xf_reaction_content currently has no index that leads with reaction_user_id combined with content_type — the existing indexes (content_type_id_user_id, ictci, etc.) all lead with content_type/content_id first, so this query can't seek efficiently even for a single deletion. A composite index on (reaction_user_id, content_type, content_id) would make each individual run of this query fast (fully covering), but wouldn't address the underlying growth-over-time problem described above.

 

[XF Bug Bot]

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.3.14).

Change log:

Rebuild recent reaction caches before reassigning a changed user's reactions, so deletions no longer scan every deleted user's reactions

There may be a delay before changes are rolled out to the XenForo Community.