Operations involving mass updating xf_reaction_content.is_counted will hang

Jawsh

Active member
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:

Code:
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.

Code:
> 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.
 
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
 
Top Bottom