XF 2.1 1 query - 2 servers and 4 seconds difference

au lait

Well-known member
Query
Code:
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 - :rolleyes::oops:


Server 1:

Run Time: 3.232797
Select TypeTableTypePossible KeysKeyKey LenRefRowsExtra
SIMPLExf_node_Node_2index_mergePRIMARY,parent_node_id,display_in_list,display_in_list_2PRIMARY,parent_node_id4,410Using union(PRIMARY,parent_node_id); Using where; Using temporary; Using filesort
SIMPLExf_forum_Forum_1eq_refPRIMARY,get_nodesPRIMARY4ah_xenforo.xf_node_Node_2.node_id1
SIMPLExf_threadALL615242Using where; Using join buffer (Block Nested Loop)
SIMPLExf_user_User_3eq_refPRIMARYPRIMARY4ah_xenforo.xf_thread.user_id1
SIMPLExf_permission_cache_content_Permissions_4eq_refPRIMARY,content_id,content_type,content,tesPRIMARY35const,const,ah_xenforo.xf_node_Node_2.node_id1Using where


Server 2:

Run Time: 0.029622
Select TypeTableTypePossible KeysKeyKey LenRefRowsExtra
SIMPLExf_threadindexlast_post_date4595007Using where
SIMPLExf_forum_Forum_1eq_refPRIMARY,get_nodesPRIMARY42androidhilfe.xf_thread.node_id1
SIMPLExf_node_Node_2eq_refPRIMARY,parent_node_id,display_in_list,display_in_list_2PRIMARY42androidhilfe.xf_thread.node_id1Using where
SIMPLExf_user_User_3eq_refPRIMARYPRIMARY42androidhilfe.xf_thread.user_id1
SIMPLExf_permission_cache_content_Permissions_4eq_refPRIMARY,content_id,content_type,content,tescontent_id352androidhilfe.xf_thread.node_id,const,const1Using where


Anybody got any ideas? Thanks😘
 

Attachments

  • Screenshot_1591185725.png
    Screenshot_1591185725.png
    26.2 KB · Views: 7

Lukas W.

Well-known member
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

Well-known member
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.


Code:
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 TypeTableTypePossible KeysKeyKey LenRefRowsExtra
SIMPLExf_node_Node_2index_mergePRIMARY,parent_node_id,display_in_list,display_in_list_2PRIMARY,parent_node_id4,410Using union(PRIMARY,parent_node_id); Using where; Using temporary; Using filesort
SIMPLExf_forum_Forum_1eq_refPRIMARY,get_nodesPRIMARY4ah_xenforo.xf_node_Node_2.node_id1
SIMPLExf_threadrefnode_id_last_post_date,node_id_sticky_state_last_post,discussion_state,discussion_typenode_id_last_post_date4ah_xenforo.xf_node_Node_2.node_id144Using where
SIMPLExf_user_User_3eq_refPRIMARYPRIMARY4ah_xenforo.xf_thread.user_id1
SIMPLExf_permission_cache_content_Permissions_4eq_refPRIMARY,content_id,content_type,content,tesPRIMARY35const,const,ah_xenforo.xf_node_Node_2.node_id1Using where
 

Lukas W.

Well-known member
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

Well-known member
Code:
 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)
 
Last edited:

au lait

Well-known member
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 😘
 
  • Like
Reactions: Xon
Top