Fixed getUserReactionsTabSummary is too slow

[K a M a L]

Affected version

2.1.x

I'm not sure if this should be considered bug or improvement request , I run a forum where

xf_reaction_content

has more than 24M records , my slow query log is full with

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

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

FORCE INDEX (content_user_id_reaction_date)

causes performance degradation.

3- Avoiding type casting
while preparing query , method

fetchPairs()

converted

$userId

to string , this prevents MySQL using index if column is indexed

 

[Mike]

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.