I'm not sure if this should be considered bug or improvement request , I run a forum where [CODE]xf_reaction_content[/CODE] has more than 24M records , my slow query log is full with
[CODE=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;[/CODE]
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`);
[/CODE]
2- Avoiding Optimizer Hints
[CODE=sql]FORCE INDEX (content_user_id_reaction_date)[/CODE]
causes performance degradation.
3- Avoiding type casting
while preparing query , method [CODE]fetchPairs()[/CODE] converted [CODE]$userId[/CODE] to string , this prevents MySQL using index if column is indexed
