XF 2.1 1 query - 2 servers and 4 seconds difference

[au lait]

Query

SELECT `xf_thread`.*, `xf_forum_Forum_1`.*, `xf_node_Node_2`.*, `xf_user_User_3`.*, `xf_permission_cache_content_Permissions_4`.*
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` = '304')
WHERE (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_node_Node_2`.`display_in_list` = 1) AND ((`xf_node_Node_2`.`node_id` IN (12)) OR (`xf_node_Node_2`.`parent_node_id` IN (12)))
ORDER BY `xf_thread`.`last_post_date` DESC
LIMIT 20

This is a 1:1 copy of the DB data (only a few days older). Only different DB versions mySQL vs mariaDB

I see that there is a temporary sorting! Indexes exist... As written a 1:1 copy on both systems. One system has no problem with that.
Attachment Indexes-Screenshot

Using Union -


Server 1:

Run Time: 3.232797

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_node_Node_2	index_merge	PRIMARY,parent_node_id,display_in_list,display_in_list_2	PRIMARY,parent_node_id	4,4		10	Using union(PRIMARY,parent_node_id); Using where; Using temporary; Using filesort
SIMPLE	xf_forum_Forum_1	eq_ref	PRIMARY,get_nodes	PRIMARY	4	ah_xenforo.xf_node_Node_2.node_id	1
SIMPLE	xf_thread	ALL					615242	Using where; Using join buffer (Block Nested Loop)
SIMPLE	xf_user_User_3	eq_ref	PRIMARY	PRIMARY	4	ah_xenforo.xf_thread.user_id	1
SIMPLE	xf_permission_cache_content_Permissions_4	eq_ref	PRIMARY,content_id,content_type,content,tes	PRIMARY	35	const,const,ah_xenforo.xf_node_Node_2.node_id	1	Using where

Server 2:

Run Time: 0.029622

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_thread	index		last_post_date	4		595007	Using where
SIMPLE	xf_forum_Forum_1	eq_ref	PRIMARY,get_nodes	PRIMARY	4	2androidhilfe.xf_thread.node_id	1
SIMPLE	xf_node_Node_2	eq_ref	PRIMARY,parent_node_id,display_in_list,display_in_list_2	PRIMARY	4	2androidhilfe.xf_thread.node_id	1	Using where
SIMPLE	xf_user_User_3	eq_ref	PRIMARY	PRIMARY	4	2androidhilfe.xf_thread.user_id	1
SIMPLE	xf_permission_cache_content_Permissions_4	eq_ref	PRIMARY,content_id,content_type,content,tes	content_id	35	2androidhilfe.xf_thread.node_id,const,const	1	Using where

Anybody got any ideas? Thanks

 

[Lukas W.]

Are they running the same MySQL version? You may want to hint or force indexes on the query to see if that improves the situation.

 

[au lait]

Only different DB versions mySQL vs mariaDB

Where it runs faster is mariaDB and my PC. Where it is slow, is a server with mySQL (Percona)
DB Version can't be changed I could only adjust the my.ini.

If I remove FORCE INDEX it will go faster! But then I get problems in other areas where the same query is! Sometimes it is with - sometimes it is good without.




If I remove FORCE INDEX it is faster! But it is far from being optimal!
0.269322 vs 0.029622

I find the temporary sorting strange.

SELECT `xf_thread`.*, `xf_forum_Forum_1`.*, `xf_node_Node_2`.*, `xf_user_User_3`.*, `xf_permission_cache_content_Permissions_4`.*
FROM `xf_thread`
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` = '304')
WHERE (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_node_Node_2`.`display_in_list` = 1) AND ((`xf_node_Node_2`.`node_id` IN (12)) OR (`xf_node_Node_2`.`parent_node_id` IN (12)))
ORDER BY `xf_thread`.`last_post_date` DESC
LIMIT 20

Run Time: 0.269322

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_node_Node_2	index_merge	PRIMARY,parent_node_id,display_in_list,display_in_list_2	PRIMARY,parent_node_id	4,4		10	Using union(PRIMARY,parent_node_id); Using where; Using temporary; Using filesort
SIMPLE	xf_forum_Forum_1	eq_ref	PRIMARY,get_nodes	PRIMARY	4	ah_xenforo.xf_node_Node_2.node_id	1
SIMPLE	xf_thread	ref	node_id_last_post_date,node_id_sticky_state_last_post,discussion_state,discussion_type	node_id_last_post_date	4	ah_xenforo.xf_node_Node_2.node_id	144	Using where
SIMPLE	xf_user_User_3	eq_ref	PRIMARY	PRIMARY	4	ah_xenforo.xf_thread.user_id	1
SIMPLE	xf_permission_cache_content_Permissions_4	eq_ref	PRIMARY,content_id,content_type,content,tes	PRIMARY	35	const,const,ah_xenforo.xf_node_Node_2.node_id	1	Using where

 

[Lukas W.]

MariaDB is essentially MySQL, but I've recently been pointed out by @Xon that the most recent version of MySQL/MariaDB has a quirk that can cause unexpectedly long query execution times.

 

[Xon]

 Using where; Using join buffer (Block Nested Loop)

Here is your problem.

Before MariaDB 10.4.x+, the setting optimizer_use_condition_selectivity=1 worked. But they changed to optimizer_use_condition_selectivity=4 which does really dumb things if the table/join estimates are wrong. (ref https://jira.mariadb.org/browse/MDEV-15253 )

Except MariaDB 10.4.x is literally incapable of getting the table/join estimate right if you join to a vastly smaller table due to an unresolved query optimizer issue that is slated to be fixed in MariaDB 10.5.x or maybe 10.6.x (raft of tickets that keep claiming to fix it, but don't)

 

[au lait]

Here is your problem.

THANK YOU!

I have the problem with mySQL (Percona 8.0.13-4).

SET OPTIMIZER_USE_CONDITION_SELECTIVITY= X;
is not working... Unknown system variable. Do you have any idea what I can do?

While I was writing this and was able to correctly ask Google by your answer/hint, I came across this set optimizer_switch='block_nested_loop=off'. This makes the query super fast

So it seems to be "fixed" for now

 

[Xon]

I've added that work-around for MySQL/ Percona 8+ to the FAQ for my Collaborative Thread's add-on which is still plagued by this issue.