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!
 
Top