Server issue XF is very slow - over 2 sek for Forum Home

MrFreeze

Member
Affected version
2.2 Beta 4
There are no add-ons installed! only 30k threads and 3 users

Code:
SELECT `xf_thread`.*, `xf_forum_Forum_1`.*, `xf_node_Node_2`.*, `xf_user_User_3`.*, `xf_permission_cache_content_Permissions_4`.*, `xf_user_LastPoster_5`.*, `xf_thread_read_Read_6`.*, `xf_forum_read_Read_7`.*

FROM `xf_thread` FORCE INDEX (`last_post_date`)

LEFT JOIN `xf_forum` AS `xf_forum_Forum_1` ON (`xf_forum_Forum_1`.`node_id` = `xf_thread`.`node_id`)

LEFT JOIN `xf_node` AS `xf_node_Node_2` ON (`xf_node_Node_2`.`node_id` = `xf_forum_Forum_1`.`node_id`)

LEFT JOIN `xf_user` AS `xf_user_User_3` ON (`xf_user_User_3`.`user_id` = `xf_thread`.`user_id`)

LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_4` ON (`xf_permission_cache_content_Permissions_4`.`content_type` = 'node' AND `xf_permission_cache_content_Permissions_4`.`content_id` = `xf_node_Node_2`.`node_id` AND `xf_permission_cache_content_Permissions_4`.`permission_combination_id` = '6')

LEFT JOIN `xf_user` AS `xf_user_LastPoster_5` ON (`xf_user_LastPoster_5`.`user_id` = `xf_thread`.`last_post_user_id`)

LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_6` ON (`xf_thread_read_Read_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_6`.`user_id` = '1')

LEFT JOIN `xf_forum_read` AS `xf_forum_read_Read_7` ON (`xf_forum_read_Read_7`.`node_id` = `xf_forum_Forum_1`.`node_id` AND `xf_forum_read_Read_7`.`user_id` = '1')

WHERE (`xf_forum_Forum_1`.`find_new` = 1) AND (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_thread`.`last_post_date` > 1596361555)

ORDER BY `xf_thread`.`last_post_date` DESC

LIMIT 10
Run Time: 2.413632
Select TypeTableTypePossible KeysKeyKey LenRefRowsExtra
SIMPLExf_threadrangelast_post_datelast_post_date437098Using index condition; Using where; Using temporary; Using filesort
SIMPLExf_forum_Forum_1ALLPRIMARY2Using where; Using join buffer (flat, BNL join)
SIMPLExf_node_Node_2eq_refPRIMARYPRIMARY4testxf2.xf_thread.node_id1
SIMPLExf_user_User_3eq_refPRIMARYPRIMARY4testxf2.xf_thread.user_id1
SIMPLExf_permission_cache_content_Permissions_4eq_refPRIMARYPRIMARY35const,const,testxf2.xf_node_Node_2.node_id1Using where
SIMPLExf_user_LastPoster_5eq_refPRIMARYPRIMARY4testxf2.xf_thread.last_post_user_id1
SIMPLExf_thread_read_Read_6eq_refuser_id_thread_id,thread_iduser_id_thread_id8const,testxf2.xf_thread.thread_id1
SIMPLExf_forum_read_Read_7eq_refuser_id_node_id,node_iduser_id_node_id8const,testxf2.xf_thread.node_id1
 

djbaxter

Well-known member
How long it takes you to load a website has nothing to do with software bugs.
To be fair, it can.

But if you are having a problem and the rest of the population using that software are not, look at your own computer system, your internet connection, and your hosting. The problem will be in one of those areas.
 

Ambient Vibe

Active member
When my forum loads slowly it is almost always a hosting issue (overloaded server, etc.) I would check your CPanel Statistics and tale a closer look there. If anything shows up in red (for me it's usually Physical Memory Usage), then your answer may lay there. Good luck!
 

Mike

XenForo developer
Staff member
I don't really understand why MySQL is giving this query plan. It looks like MySQL is simply processing the query incorrectly. We've had this issue before, which is why the index hint is in there.

There isn't really any reason for MySQL to be using a temporary table here as it can traverse in the index order and with the limit, it normally shouldn't get very far. It's possible that it doesn't think that's the case. This may reflect non-ideal MySQL settings, for example (not having access to enough memory, extremely limited buffer sizes, etc).

If you're unable to change these things for your environment, you may need to remove the new posts widget from this page.
 
Top