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

[MrFreeze]

Affected version

2.2 Beta 4

There are no add-ons installed! only 30k threads and 3 users

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 Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_thread	range	last_post_date	last_post_date	4		37098	Using index condition; Using where; Using temporary; Using filesort
SIMPLE	xf_forum_Forum_1	ALL	PRIMARY				2	Using where; Using join buffer (flat, BNL join)
SIMPLE	xf_node_Node_2	eq_ref	PRIMARY	PRIMARY	4	testxf2.xf_thread.node_id	1
SIMPLE	xf_user_User_3	eq_ref	PRIMARY	PRIMARY	4	testxf2.xf_thread.user_id	1
SIMPLE	xf_permission_cache_content_Permissions_4	eq_ref	PRIMARY	PRIMARY	35	const,const,testxf2.xf_node_Node_2.node_id	1	Using where
SIMPLE	xf_user_LastPoster_5	eq_ref	PRIMARY	PRIMARY	4	testxf2.xf_thread.last_post_user_id	1
SIMPLE	xf_thread_read_Read_6	eq_ref	user_id_thread_id,thread_id	user_id_thread_id	8	const,testxf2.xf_thread.thread_id	1
SIMPLE	xf_forum_read_Read_7	eq_ref	user_id_node_id,node_id	user_id_node_id	8	const,testxf2.xf_thread.node_id	1

 

[djbaxter]

What sort of hosting are you using and what are the specs for that hosting?

 

[Ntown]

How long it takes you to load a website has nothing to do with software bugs.

 

[djbaxter]

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.

 

[BassMan]

What software bugs? I have several forums that run XenForo and works with no problems.

 

[Ambient Vibe]

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]

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.

 

[mc nabulsy]

LIMIT 10
Run Time: 2.413632

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_thread	range	last_post_date	last_post_date	4	37098	Using index condition; Using where; Using temporary; Using filesort
SIMPLE	xf_forum_Forum_1	ALL	PRIMARY	2	Using where; Using join buffer (flat, BNL join)
SIMPLE	xf_node_Node_2	eq_ref	PRIMARY	PRIMARY	4	testxf2.xf_thread.node_id	1
SIMPLE	xf_user_User_3	eq_ref	PRIMARY	PRIMARY	4	testxf2.xf_thread.user_id	1
SIMPLE	xf_permission_cache_content_Permissions_4	eq_ref	PRIMARY	PRIMARY	35	const,const,testxf2.xf_node_Node_2.node_id	1	Using where
SIMPLE	xf_user_LastPoster_5	eq_ref	PRIMARY	PRIMARY	4	testxf2.xf_thread.last_post_user_id	1
SIMPLE	xf_thread_read_Read_6	eq_ref	user_id_thread_id,thread_id	user_id_thread_id	8	const,testxf2.xf_thread.thread_id	1
SIMPLE	xf_forum_read_Read_7	eq_ref	user_id_node_id,node_id	user_id_node_id	8	const,testxf2.xf_thread.node_id

from where i can find this table and edit it?