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

Affected version
2.1.2

Xon

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

SQL:
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;
Code:
+------+-------------+----------+-------+-------------------------------------------+-------------+---------+-------------------------------------+------+----------------------------------------------+
| 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

XenForo bug fixer bot
Staff member
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.
 
Top