Fixed New Threads Widget causing Slow Query

Affected version
2.1.3

Kirby

Well-known member
We are using the New Threads Widget to display threads from our news forum on a page.
(Max Entries = 2, Expanded, Only one forum selected)

This forum does not have many threads, yet the query does take way too long (over 200ms).

This seems to happen as the Widget uses \XF\Repository\Thread::findLatestThreads() white forces index post_date which in turn causes a full table scan.

Code:
| id   | select_type | table                                     | type   | possible_keys                     | key               | key_len | ref                                                 | rows   | Extra                       |
+------+-------------+-------------------------------------------+--------+-----------------------------------+-------------------+---------+-----------------------------------------------------+--------+-----------------------------+
|    1 | SIMPLE      | xf_thread                                 | ALL    | NULL                              | NULL              | NULL    | NULL                                                | 104556 | Using where; Using filesort |
|    1 | SIMPLE      | xf_forum_Forum_1                          | const  | PRIMARY                           | PRIMARY           | 4       | const                                               |      1 |                             |
|    1 | SIMPLE      | xf_node_Node_2                            | eq_ref | PRIMARY                           | PRIMARY           | 4       | xf_db.xf_forum_Forum_1.node_id           |      1 | Using where                 |
|    1 | SIMPLE      | xf_user_User_3                            | eq_ref | PRIMARY                           | PRIMARY           | 4       | xf_db.xf_thread.user_id                  |      1 |                             |
|    1 | SIMPLE      | xf_permission_cache_content_Permissions_4 | eq_ref | PRIMARY                           | PRIMARY           | 35      | const,const,xf_db.xf_node_Node_2.node_id |      1 | Using where                 |
|    1 | SIMPLE      | xf_user_LastPoster_5                      | eq_ref | PRIMARY                           | PRIMARY           | 4       | xf_db.xf_thread.last_post_user_id        |      1 |                             |
|    1 | SIMPLE      | xf_thread_read_Read_6                     | eq_ref | user_id_thread_id,thread_id       | user_id_thread_id | 8       | const,xf_db.xf_thread.thread_id          |      1 |                             |
|    1 | SIMPLE      | xf_thread_user_post_UserPosts_7           | eq_ref | PRIMARY,user_id                   | PRIMARY           | 8       | xf_db.xf_thread.thread_id,const          |      1 |                             |
|    1 | SIMPLE      | xf_thread_watch_Watch_8                   | eq_ref | PRIMARY,thread_id_email_subscribe | PRIMARY           | 8       | const,xf_db.xf_thread.thread_id          |      1 |                             |
|    1 | SIMPLE      | xf_forum_read_Read_9                      | eq_ref | user_id_node_id,node_id           | user_id_node_id   | 8       | const,xf_db.xf_forum_Forum_1.node_id     |      1 | Using where                 |
|    1 | SIMPLE      | xf_forum_watch_Watch_10                   | eq_ref | PRIMARY,node_id_notify_on         | PRIMARY           | 8       | const,xf_db.xf_forum_Forum_1.node_id     |      1 | Using where                 |
|    1 | SIMPLE      | xf_post_FirstPost_11                      | eq_ref | PRIMARY                           | PRIMARY           | 4       | xf_db.xf_thread.first_post_id            |      1 |                             |
+------+-------------+-------------------------------------------+--------+-----------------------------------+-------------------+---------+-----------------------------------------------------+--------+-----------------------------+
Without the forced index the plan looks way better and the query time is < 2 ms
Code:
+------+-------------+-------------------------------------------+--------+-------------------------------------------------------+------------------------+---------+-----------------------------------------------------+------+-----------------------------+
| id   | select_type | table                                     | type   | possible_keys                                         | key                    | key_len | ref                                                 | rows | Extra                       |
+------+-------------+-------------------------------------------+--------+-------------------------------------------------------+------------------------+---------+-----------------------------------------------------+------+-----------------------------+
|    1 | SIMPLE      | xf_thread                                 | ref    | node_id_last_post_date,node_id_sticky_state_last_post | node_id_last_post_date | 4       | const                                               |    7 | Using where; Using filesort |
|    1 | SIMPLE      | xf_forum_Forum_1                          | const  | PRIMARY                                               | PRIMARY                | 4       | const                                               |    1 |                             |
|    1 | SIMPLE      | xf_node_Node_2                            | eq_ref | PRIMARY                                               | PRIMARY                | 4       | xf_db.xf_forum_Forum_1.node_id           |    1 | Using where                 |
|    1 | SIMPLE      | xf_user_User_3                            | eq_ref | PRIMARY                                               | PRIMARY                | 4       | xf_db.xf_thread.user_id                  |    1 |                             |
|    1 | SIMPLE      | xf_permission_cache_content_Permissions_4 | eq_ref | PRIMARY                                               | PRIMARY                | 35      | const,const,xf_db.xf_node_Node_2.node_id |    1 | Using where                 |
|    1 | SIMPLE      | xf_user_LastPoster_5                      | eq_ref | PRIMARY                                               | PRIMARY                | 4       | xf_db.xf_thread.last_post_user_id        |    1 |                             |
|    1 | SIMPLE      | xf_thread_read_Read_6                     | eq_ref | user_id_thread_id,thread_id                           | user_id_thread_id      | 8       | const,xf_db.xf_thread.thread_id          |    1 |                             |
|    1 | SIMPLE      | xf_thread_user_post_UserPosts_7           | eq_ref | PRIMARY,user_id                                       | PRIMARY                | 8       | xf_db.xf_thread.thread_id,const          |    1 |                             |
|    1 | SIMPLE      | xf_thread_watch_Watch_8                   | eq_ref | PRIMARY,thread_id_email_subscribe                     | PRIMARY                | 8       | const,xf_db.xf_thread.thread_id          |    1 |                             |
|    1 | SIMPLE      | xf_forum_read_Read_9                      | eq_ref | user_id_node_id,node_id                               | user_id_node_id        | 8       | const,xf_db.xf_forum_Forum_1.node_id     |    1 | Using where                 |
|    1 | SIMPLE      | xf_forum_watch_Watch_10                   | eq_ref | PRIMARY,node_id_notify_on                             | PRIMARY                | 8       | const,xf_db.xf_forum_Forum_1.node_id     |    1 | Using where                 |
|    1 | SIMPLE      | xf_post_FirstPost_11                      | eq_ref | PRIMARY                                               | PRIMARY                | 4       | xf_db.xf_thread.first_post_id            |    1 |                             |
+------+-------------+-------------------------------------------+--------+-------------------------------------------------------+------------------------+---------+-----------------------------------------------------+------+-----------------------------+
It would be nice if it was also possible to set a cut-off date, this could help other cases where the index can be used.
 

XF Bug Bot

XenForo bug fixer bot
Staff member
Thank you for reporting this issue. It has now been resolved and we are aiming to include it in a future XF release (2.1.4).

Change log:
Remove the forced index hint for latest threads widget and add a new (post) date limit option.
Any changes made as a result of this issue being resolved may not be rolled out here until later.
 
Top