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

nocte

Well-known member
Affected version
2.1.x
I had a similar issue as described here:


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

SQL:
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:

SQL:
ALTER TABLE `xf_reaction_content` ADD INDEX `reaction_user_id` (`reaction_user_id`) USING BTREE;
 
I created indexes many years ago for vB, and now for xenforo too... :)

one more trick for "batch deleting issue"

SQL:
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 :D
 
Last edited:
Top Bottom