Server issue Watched thread query takes long time

[MilkyMeda]

Affected version

2.1.3

Reproduce:

1. Create new widget
2. Choose "New posts"
3. Check:
   • Forum list: Sidebar​
   • Watched​
4. Save.
5. Now, go to forum list and watch the query time... Takes about 3 to 5 seconds!

Update: I haven't been able to reproduce it on a test user which only has 2 pages of watched threads. My main account has 1300 and it takes at least 3 seconds to load.

 

[Mike]

Doing some testing here and while this isn't a great query, there isn't really a direct way to optimize it and I don't think it should be that bad overall. This query taking 3-5 seconds with 1300 threads likely points to other bottlenecks on the server (likely in MySQL's configuration and/or something like disk speed).

For reference, the query run here is basically identical to the query run when viewing your list of watched threads. You can check how it performs there. On the XF community, where I appear to have about 4500 threads watched, the query takes less than 0.1 seconds to run. If it's taking 3-5 seconds, the only non-server'y element I can think of is MySQL coming up with the wrong query plan, but that feels unlikely to me. If you want to check that, if you enable debug mode and look at the query output for your watched threads page, the EXPLAIN output for the major query here should likely start with the xf_thread_watch table. If it starts with xf_thread, I could potentially see that being the issue, though I'd guess that would still be triggered for users with few watched threads.

 

[MilkyMeda]

Here are the major queries:

SELECT COUNT(*) FROM `xf_thread` INNER JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_5` ON (`xf_thread_watch_Watch_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_5`.`user_id` = '1') WHERE (`xf_thread`.`discussion_state` = 'visible')
Run Time: 1.401076

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_thread_watch_Watch_5	ref	PRIMARY,thread_id_email_subscribe	PRIMARY	4	const	50784	Using index
SIMPLE	xf_thread	eq_ref	PRIMARY	PRIMARY	4	xenforo2.xf_thread_watch_Watch_5.thread_id	1	Using where

SELECT `xf_thread`.*, `xf_user_User_1`.*, `xf_user_LastPoster_2`.*, `xf_thread_read_Read_3`.*, `xf_thread_user_post_UserPosts_4`.*, `xf_thread_watch_Watch_5`.*, `xf_forum_Forum_6`.*, `xf_node_Node_7`.*, `xf_forum_read_Read_8`.*, `xf_forum_watch_Watch_9`.* FROM `xf_thread` LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_thread`.`user_id`) LEFT JOIN `xf_user` AS `xf_user_LastPoster_2` ON (`xf_user_LastPoster_2`.`user_id` = `xf_thread`.`last_post_user_id`) LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_3` ON (`xf_thread_read_Read_3`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_3`.`user_id` = '1') LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_4` ON (`xf_thread_user_post_UserPosts_4`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_4`.`user_id` = '1') INNER JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_5` ON (`xf_thread_watch_Watch_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_5`.`user_id` = '1') LEFT JOIN `xf_forum` AS `xf_forum_Forum_6` ON (`xf_forum_Forum_6`.`node_id` = `xf_thread`.`node_id`) LEFT JOIN `xf_node` AS `xf_node_Node_7` ON (`xf_node_Node_7`.`node_id` = `xf_forum_Forum_6`.`node_id`) LEFT JOIN `xf_forum_read` AS `xf_forum_read_Read_8` ON (`xf_forum_read_Read_8`.`node_id` = `xf_forum_Forum_6`.`node_id` AND `xf_forum_read_Read_8`.`user_id` = '1') LEFT JOIN `xf_forum_watch` AS `xf_forum_watch_Watch_9` ON (`xf_forum_watch_Watch_9`.`node_id` = `xf_forum_Forum_6`.`node_id` AND `xf_forum_watch_Watch_9`.`user_id` = '1') WHERE (`xf_thread`.`discussion_state` = 'visible') ORDER BY `xf_thread`.`last_post_date` DESC LIMIT 20
Run Time: 2.371716

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_thread_watch_Watch_5	ref	PRIMARY,thread_id_email_subscribe	PRIMARY	4	const	50784	Using temporary; Using filesort
SIMPLE	xf_thread	eq_ref	PRIMARY	PRIMARY	4	xenforo2.xf_thread_watch_Watch_5.thread_id	1	Using where
SIMPLE	xf_forum_Forum_6	eq_ref	PRIMARY	PRIMARY	4	xenforo2.xf_thread.node_id	1
SIMPLE	xf_node_Node_7	eq_ref	PRIMARY	PRIMARY	4	xenforo2.xf_forum_Forum_6.node_id	1	Using where
SIMPLE	xf_forum_read_Read_8	eq_ref	user_id_node_id,node_id	user_id_node_id	8	const,xenforo2.xf_forum_Forum_6.node_id	1	Using where
SIMPLE	xf_forum_watch_Watch_9	eq_ref	PRIMARY,node_id_notify_on	PRIMARY	8	const,xenforo2.xf_forum_Forum_6.node_id	1	Using where
SIMPLE	xf_user_User_1	eq_ref	PRIMARY	PRIMARY	4	xenforo2.xf_thread.user_id	1
SIMPLE	xf_user_LastPoster_2	eq_ref	PRIMARY	PRIMARY	4	xenforo2.xf_thread.last_post_user_id	1
SIMPLE	xf_thread_read_Read_3	eq_ref	user_id_thread_id,thread_id	user_id_thread_id	8	const,xenforo2.xf_thread_watch_Watch_5.thread_id	1
SIMPLE	xf_thread_user_post_UserPosts_4	eq_ref	PRIMARY,user_id	PRIMARY	8	xenforo2.xf_thread_watch_Watch_5.thread_id,const	1

 

[MilkyMeda]

BTW, I meant 1300 pages, not 1300 threads, my mistake. I have 26.400 watched threads which means 1320 pages.