- Affected version
- 2.2.9
Opening Article forum having Preview display style consisting of around 50,000 articles is painfully slow.
If change Display style from Preview to Standard, it opens instantly.
Had to change "Thread list date limit" to 1 year to speed it up. So instead of 1636, pagination shows 16 pages.
Here is the slow query (8 seconds):
It executes almost instantly when remove:
Explain shows:
If change Display style from Preview to Standard, it opens instantly.
Had to change "Thread list date limit" to 1 year to speed it up. So instead of 1636, pagination shows 16 pages.
Here is the slow query (8 seconds):
Code:
SELECT `xf_thread`.*, `xf_deletion_log_DeletionLog_1`.*, `xf_user_User_2`.*, `xf_user_LastPoster_3`.*, `xf_thread_read_Read_4`.*, `xf_thread_user_post_UserPosts_5`.*, `xf_thread_watch_Watch_6`.*, `xf_post_FirstPost_7`.*, `xf_user_User_8`.*, `xf_user_option_Option_9`.*, `xf_user_profile_Profile_10`.*, `xf_user_privacy_Privacy_11`.*, `xf_permission_combination_PermissionCombination_12`.*, `xf_session_activity_Activity_13`.*, `xf_reaction_content_Reactions_14`.*, `xf_bookmark_item_Bookmarks_15`.*
FROM `xf_thread`
LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_1` ON (`xf_deletion_log_DeletionLog_1`.`content_type` = 'thread' AND `xf_deletion_log_DeletionLog_1`.`content_id` = `xf_thread`.`thread_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_thread`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_LastPoster_3` ON (`xf_user_LastPoster_3`.`user_id` = `xf_thread`.`last_post_user_id`)
LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_4` ON (`xf_thread_read_Read_4`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_4`.`user_id` = '14')
LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_5` ON (`xf_thread_user_post_UserPosts_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_5`.`user_id` = '14')
LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_6` ON (`xf_thread_watch_Watch_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_6`.`user_id` = '14')
LEFT JOIN `xf_post` AS `xf_post_FirstPost_7` ON (`xf_post_FirstPost_7`.`post_id` = `xf_thread`.`first_post_id`)
LEFT JOIN `xf_user` AS `xf_user_User_8` ON (`xf_user_User_8`.`user_id` = `xf_post_FirstPost_7`.`user_id`)
LEFT JOIN `xf_user_option` AS `xf_user_option_Option_9` ON (`xf_user_option_Option_9`.`user_id` = `xf_user_User_8`.`user_id`)
LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_10` ON (`xf_user_profile_Profile_10`.`user_id` = `xf_user_User_8`.`user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_11` ON (`xf_user_privacy_Privacy_11`.`user_id` = `xf_user_User_8`.`user_id`)
LEFT JOIN `xf_permission_combination` AS `xf_permission_combination_PermissionCombination_12` ON (`xf_permission_combination_PermissionCombination_12`.`permission_combination_id` = `xf_user_User_8`.`permission_combination_id`)
LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_13` ON (`xf_session_activity_Activity_13`.`user_id` = `xf_user_User_8`.`user_id` AND `xf_session_activity_Activity_13`.`unique_key` = CONCAT(`xf_user_User_8`.`user_id`, ''))
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_14` ON (`xf_reaction_content_Reactions_14`.`content_type` = 'post' AND `xf_reaction_content_Reactions_14`.`content_id` = `xf_post_FirstPost_7`.`post_id` AND `xf_reaction_content_Reactions_14`.`reaction_user_id` = '14')
LEFT JOIN `xf_bookmark_item` AS `xf_bookmark_item_Bookmarks_15` ON (`xf_bookmark_item_Bookmarks_15`.`content_type` = 'post' AND `xf_bookmark_item_Bookmarks_15`.`content_id` = `xf_post_FirstPost_7`.`post_id` AND `xf_bookmark_item_Bookmarks_15`.`user_id` = '14')
WHERE (`xf_thread`.`node_id` = 397) AND ((`xf_thread`.`discussion_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_thread`.`sticky` = 0)
ORDER BY `xf_thread`.`last_post_date` DESCIt executes almost instantly when remove:
Code:
ORDER BY `xf_thread`.`last_post_date` DESCExplain shows:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | xf_thread | range | node_id_last_post_date,node_id_sticky_state_last_post,node_id_sticky_state_vote_score | node_id_sticky_state_last_post | 6 | NULL | 94091 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | xf_deletion_log_DeletionLog_1 | eq_ref | PRIMARY | PRIMARY | 31 | const,forum.xf_thread.thread_id | 1 | Using where |
| 1 | SIMPLE | xf_user_User_2 | eq_ref | PRIMARY | PRIMARY | 4 | forum.xf_thread.user_id | 1 | |
| 1 | SIMPLE | xf_user_LastPoster_3 | eq_ref | PRIMARY | PRIMARY | 4 | forum.xf_thread.last_post_user_id | 1 | |
| 1 | SIMPLE | xf_thread_read_Read_4 | eq_ref | user_id_thread_id,thread_id | user_id_thread_id | 8 | const,forum.xf_thread.thread_id | 1 | |
| 1 | SIMPLE | xf_thread_user_post_UserPosts_5 | eq_ref | PRIMARY,user_id | PRIMARY | 8 | forum.xf_thread.thread_id,const | 1 | |
| 1 | SIMPLE | xf_thread_watch_Watch_6 | eq_ref | PRIMARY,thread_id_email_subscribe | PRIMARY | 8 | const,forum.xf_thread.thread_id | 1 | |
| 1 | SIMPLE | xf_post_FirstPost_7 | eq_ref | PRIMARY | PRIMARY | 4 | forum.xf_thread.first_post_id | 1 | |
| 1 | SIMPLE | xf_user_User_8 | eq_ref | PRIMARY | PRIMARY | 4 | forum.xf_post_FirstPost_7.user_id | 1 | Using where |
| 1 | SIMPLE | xf_user_option_Option_9 | eq_ref | PRIMARY | PRIMARY | 4 | forum.xf_user_User_8.user_id | 1 | Using where |
| 1 | SIMPLE | xf_user_profile_Profile_10 | eq_ref | PRIMARY | PRIMARY | 4 | forum.xf_user_User_8.user_id | 1 | Using where |
| 1 | SIMPLE | xf_user_privacy_Privacy_11 | eq_ref | PRIMARY | PRIMARY | 4 | forum.xf_user_User_8.user_id | 1 | Using where |
| 1 | SIMPLE | xf_permission_combination_PermissionCombination_12 | eq_ref | PRIMARY | PRIMARY | 4 | forum.xf_user_User_8.permission_combination_id | 1 | Using where |
| 1 | SIMPLE | xf_session_activity_Activity_13 | eq_ref | PRIMARY | PRIMARY | 22 | forum.xf_user_User_8.user_id,func | 1 | Using where |
| 1 | SIMPLE | xf_reaction_content_Reactions_14 | eq_ref | content_type_id_user_id,content_type_id_reaction_date,reaction_user_id_reaction_date,reaction_user_id | content_type_id_user_id | 35 | const,forum.xf_post_FirstPost_7.post_id,const | 1 | Using where |
| 1 | SIMPLE | xf_bookmark_item_Bookmarks_15 | range | user_id_content_type_content_id,content_type_content_id | user_id_content_type_content_id | 31 | NULL | 1 | Using where; Using join buffer (flat, BNL join) |
