Fixed Very poor performance on "reactions received" page for large forums

[Xon]

Affected version

2.1.2

The function getUserReactionsTabSummary causes poor performance due to MySQL picking the wrong index to query on;

SELECT content.reaction_id, COUNT(*) 
FROM xf_reaction_content AS content 
INNER JOIN xf_reaction AS reaction ON (content.reaction_id = reaction.reaction_id) 
WHERE content.content_user_id = '7' AND reaction.active = 1 AND content.is_counted = 1 
GROUP BY content.reaction_id 
ORDER BY reaction.display_order;

Explain output;

+------+-------------+----------+-------+-------------------------------------------+-------------+---------+-------------------------------------+------+----------------------------------------------+
| id   | select_type | table    | type  | possible_keys                             | key         | key_len | ref                                 | rows | Extra                                        |
+------+-------------+----------+-------+-------------------------------------------+-------------+---------+-------------------------------------+------+----------------------------------------------+
|    1 | SIMPLE      | reaction | index | PRIMARY                                   | PRIMARY     | 4       | NULL                                |   12 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | content  | ref   | reaction_id,content_user_id_reaction_date | reaction_id | 4       | reaction.reaction_id                | 3844 | Using where                                  |
+------+-------------+----------+-------+-------------------------------------------+-------------+---------+-------------------------------------+------+----------------------------------------------+

Picking primary effectively results in a full-table scan, and is absolutely not the row count it expects!

 

[XF Bug Bot]

Thank you for reporting this issue. It has now been resolved and we are aiming to include it in a future XF release (2.1.3).

Change log:

Hint index when fetching user content reactions

Any changes made as a result of this issue being resolved may not be rolled out here until later.