Fixed Add an index to `xf_reaction_content`.`reaction_user_id`

[nocte]

Affected version

2.1.x

I had a similar issue as described here:

XF 2.1 - Mass errors when batch deleting

Not sure is it bug or just my local problem (should I open support ticket?). I did some cleanup and batch deleted old unused acccounts (≈200). Soon after that cpu usage is grown and I got ton of errors like this: XF\Db\Exception: Job XF:UserDeleteCleanUp: MySQL query error [1205]: Lock wait...

xenforo.com

Running this query with a random unused user id took bout 40 seconds. This query runs with ever user deletion:

UPDATE IGNORE `xf_reaction_content` SET `reaction_user_id` = 0 WHERE `reaction_user_id` = ?

After adding an index to the column the same query (tested with different user id's) took about 0.0015 seconds

So, I think this index should be added and this can be called a bug.

For all having troubles like this: run this query:

ALTER TABLE `xf_reaction_content` ADD INDEX `reaction_user_id` (`reaction_user_id`) USING BTREE;

 

[cyberdyne]

I created indexes many years ago for vB, and now for xenforo too...

one more trick for "batch deleting issue"

ALTER TABLE `xf_conversation_master` ADD INDEX `last_message_user_id` (`last_message_user_id`) USING BTREE;
ALTER TABLE `xf_conversation_user` ADD INDEX `last_message_username` (`last_message_username`) USING BTREE;

in my case it was fullscan for 21 million reactions, and 1.6 million of xf_conversations

 

[Mike]

Worth mentioning that 2.2 has now added this index, amongst a selection of other ones that help with things like renames or user deletes.

 

[rdn]

Thanks Mike, I hope no conflict for us who already run the query above.