- Affected version
- 2.1.3
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
Without the forced index the plan looks way better and the query time is < 2 ms
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.
(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.
