- Affected version
- 2.3.9
Code:
SELECT `xf_profile_post`.*, `xf_user_ProfileUser_1`.*, `xf_user_privacy_Privacy_2`.*, `xf_user_User_3`.*, `xf_reaction_content_Reactions_4`.*
FROM `xf_profile_post` USE INDEX (`post_date`)
LEFT JOIN `xf_user` AS `xf_user_ProfileUser_1` ON (`xf_user_ProfileUser_1`.`user_id` = `xf_profile_post`.`profile_user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_2` ON (`xf_user_privacy_Privacy_2`.`user_id` = `xf_user_ProfileUser_1`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_User_3` ON (`xf_user_User_3`.`user_id` = `xf_profile_post`.`user_id`)
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_4` ON (`xf_reaction_content_Reactions_4`.`content_type` = 'profile_post' AND `xf_reaction_content_Reactions_4`.`content_id` = `xf_profile_post`.`profile_post_id` AND `xf_reaction_content_Reactions_4`.`reaction_user_id` = '<redacted>')
WHERE (`xf_profile_post`.`message_state` = 'visible')
ORDER BY `xf_profile_post`.`post_date` DESC
LIMIT 10
+------+-------------+---------------------------------+--------------+------------------------------------------------------------------------+------------------------------------------------+---------+--------------------------------------------+--------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------------------+--------------+------------------------------------------------------------------------+------------------------------------------------+---------+--------------------------------------------+--------+---------------------------------------------------------------------+
| 1 | SIMPLE | xf_profile_post | ALL | NULL | NULL | NULL | NULL | 190620 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | xf_user_ProfileUser_1 | eq_ref | PRIMARY | PRIMARY | 4 | <redacted>.xf_profile_post.profile_user_id | 1 | |
| 1 | SIMPLE | xf_user_privacy_Privacy_2 | eq_ref | PRIMARY | PRIMARY | 4 | <redacted>.xf_user_ProfileUser_1.user_id | 1 | Using where |
| 1 | SIMPLE | xf_user_User_3 | eq_ref | PRIMARY | PRIMARY | 4 | <redacted>.xf_profile_post.user_id | 1 | |
| 1 | SIMPLE | xf_reaction_content_Reactions_4 | range|filter | content_type_id_user_id,content_type_id_reaction_date,reaction_user_id | reaction_user_id|content_type_id_reaction_date | 4|27 | NULL | 1 (0%) | Using where; Using join buffer (flat, BNL join); Using rowid filter |
+------+-------------+---------------------------------+--------------+------------------------------------------------------------------------+------------------------------------------------+---------+--------------------------------------------+--------+---------------------------------------------------------------------+This query scans way too many rows.
Suggested Fix
Add a cut-off setting like it already exists for the New Threads widget.
