Fixed getUserReactionsTabSummary is too slow

K a M a L

Well-known member
Affected version
2.1.x
I'm not sure if this should be considered bug or improvement request , I run a forum where
Code:
xf_reaction_content
has more than 24M records , my slow query log is full with
SQL:
SELECT content.reaction_id, COUNT(*)
            FROM xf_reaction_content AS content
            FORCE INDEX (content_user_id_reaction_date)
            INNER JOIN xf_reaction AS reaction ON
                (content.reaction_id = reaction.reaction_id)
            WHERE content.content_user_id = '125'
                AND reaction.active = 1
                AND content.is_counted = 1
            GROUP BY content.reaction_id
            ORDER BY reaction.display_order;

This query takes about 16 seconds on a powerful server
after applying some optimizations it now takes only 0.2 seconds
suggested optimizations include
1 - creating optimal indexes
Code:
ALTER TABLE `xf_reaction` ADD INDEX `xf_reaction_idx_active_reaction_id` (`active`,`reaction_id`);
ALTER TABLE `xf_reaction_content` ADD INDEX `xf_reaction_content_idx_content_id_is_counted_reaction_id` (`content_user_id`,`is_counted`,`reaction_id`);

2- Avoiding Optimizer Hints
SQL:
FORCE INDEX (content_user_id_reaction_date)
causes performance degradation.

3- Avoiding type casting
while preparing query , method
Code:
fetchPairs()
converted
Code:
$userId
to string , this prevents MySQL using index if column is indexed
 
Worth mentioning that we have added indexes in 2.2 (we generally only do alters on big tables in second point releases).

They are a little different than here, though there are also some other changes to the tab summary query. For example, we haven't added the xf_reaction index mentioned and I don't actually think it ultimately does much. We have added an index to xf_reaction_content to help here, including reaction_date at the end which should also help with browsing the specific reaction tab as well.
 
Back
Top Bottom