Operations involving mass updating xf_reaction_content.is_counted will hang

[Jawsh]

Affected version

2.2.7 Patch 1

Moderator accidentally soft-deletes thread that is 1,300 pages long. When attempting to undelete it, this query starts:

UPDATE  `xf_reaction_content` SET `is_counted` = '1' WHERE content_type = 'post' AND content_id IN (88987 ids) AND is_counted = '0'

These are the indexes on my table. I'm not sure which ones I added myself if any. I remember adding more indexes because my table is 79,770,962 rows long. It's frequently a problem child when dealing with the database and if a default XenForo feature breaks it's usually because of the reaction content table.

> SHOW INDEX FROM xf_reaction_content;
+---------------------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table               | Non_unique | Key_name                      | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------------------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| xf_reaction_content |          0 | PRIMARY                       |            1 | reaction_content_id | A         |    75557483 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          0 | content_type_id_user_id       |            1 | content_type        | A         |         698 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          0 | content_type_id_user_id       |            2 | content_id          | A         |    18889370 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          0 | content_type_id_user_id       |            3 | reaction_user_id    | A         |    75557483 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | content_type_id_reaction_date |            1 | content_type        | A         |        1406 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | content_type_id_reaction_date |            2 | content_id          | A         |    18889370 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | content_type_id_reaction_date |            3 | reaction_date       | A         |    75557483 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | content_user_id_reaction_date |            1 | content_user_id     | A         |      249364 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | content_user_id_reaction_date |            2 | reaction_date       | A         |    75557483 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | reaction_date                 |            1 | reaction_date       | A         |    75557483 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | content_type_id               |            1 | content_type        | A         |        2432 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | content_type_id               |            2 | content_id          | A         |    25185827 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | reaction_user_id              |            1 | content_user_id     | A         |      358092 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | reaction_user_id              |            2 | is_counted          | A         |      490633 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | reaction_user_id              |            3 | reaction_id         | A         |      888911 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| xf_reaction_content |          1 | reaction_user_id              |            4 | reaction_date       | A         |    75557483 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------------------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
16 rows in set (0.000 sec)

The reaction_user_id index does contain is_counted but it also contains content_user_id which this query does not use. When I simply remove the AND `is_counted`=0, the process does complete:

Query OK, 88987 rows affected (1 min 53.386 sec)
Rows matched: 88987 Changed: 88987 Warnings: 0

This is still too slow to be done via HTTP but it works. Running this query then made the original query complete in seconds.

This should be something relegated to a CRON job. It's not an urgent thing that must complete for the thread to be restored.

 

[Xon]

This is probably something which should be done as page through the data results too.

There are a number of moderator actions which assume the post-counts involved are very low and can cause some serious performance issues if used on a large forum with a large set of posts