New Profile Posts widget causes slow query

[Kirby]

Affected version

2.3.9

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.

 

[Xon]

@Kirby what indexes does the xf_profile_post table have?

Also, what is the output of analyze, as that reports the actual rows touched.

 

[Kirby]

The table is stock XenForo

CREATE TABLE `xf_profile_post` (
  `profile_post_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `profile_user_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `username` varchar(50) NOT NULL,
  `post_date` int(10) unsigned NOT NULL,
  `message` mediumtext NOT NULL,
  `ip_id` int(10) unsigned NOT NULL DEFAULT 0,
  `message_state` enum('visible','moderated','deleted') NOT NULL DEFAULT 'visible',
  `attach_count` smallint(5) unsigned NOT NULL DEFAULT 0,
  `reaction_score` int(11) NOT NULL DEFAULT 0,
  `reactions` blob DEFAULT NULL,
  `reaction_users` blob NOT NULL,
  `comment_count` int(10) unsigned NOT NULL DEFAULT 0,
  `first_comment_date` int(10) unsigned NOT NULL DEFAULT 0,
  `last_comment_date` int(10) unsigned NOT NULL DEFAULT 0,
  `latest_comment_ids` blob NOT NULL,
  `warning_id` int(10) unsigned NOT NULL DEFAULT 0,
  `warning_message` varchar(255) NOT NULL DEFAULT '',
  `embed_metadata` blob DEFAULT NULL,
  PRIMARY KEY (`profile_post_id`),
  KEY `profile_user_id_post_date` (`profile_user_id`,`post_date`),
  KEY `user_id` (`user_id`),
  KEY `post_date` (`post_date`)
)

 ANALYZE 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   | r_rows    | filtered | r_filtered | Extra                                                               |
+------+-------------+---------------------------------+--------------+------------------------------------------------------------------------+------------------------------------------------+---------+--------------------------------------------+--------+-----------+----------+------------+---------------------------------------------------------------------+
|    1 | SIMPLE      | xf_profile_post                 | ALL          | NULL                                                                   | NULL                                           | NULL    | NULL                                       | 190620 | 197752.00 |   100.00 |      99.88 | 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.00      |   100.00 |     100.00 |                                                                     |
|    1 | SIMPLE      | xf_user_privacy_Privacy_2       | eq_ref       | PRIMARY                                                                | PRIMARY                                        | 4       | <redacted>.xf_user_ProfileUser_1.user_id   | 1      | 1.00      |   100.00 |     100.00 | Using where                                                         |
|    1 | SIMPLE      | xf_user_User_3                  | eq_ref       | PRIMARY                                                                | PRIMARY                                        | 4       | <redacted>.xf_profile_post.user_id         | 1      | 0.94      |   100.00 |     100.00 |                                                                     |
|    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%) | 0.00 (0%) |     0.41 |     100.00 | Using where; Using join buffer (flat, BNL join); Using rowid filter |
+------+-------------+---------------------------------+--------------+------------------------------------------------------------------------+------------------------------------------------+---------+--------------------------------------------+--------+-----------+----------+------------+---------------------------------------------------------------------+

 

[Xon]

The database is ignoring the use index, it might need to be force index