what is difference 10.6 and 10.11 version.
When the same data was selected in versions 10.6 and 10.11, the execution plan and speed were different. Why and how to fix it?
EXPLAIN SELECT x.userid, x.contact, x.position, x.auth, y.groupid, z.group_name, t.from_network_id, t.to_network_id FROM user_tab x FORCE INDEX (USERID) LEFT OUTER JOIN user_group_member_tab Y ON x.userid = y.userid LEFT OUTER JOIN user_group_tab z ON y.groupid = z.groupid, network_direction_tab t FORCE INDEX (PROHIBIT_DIRECTION) WHERE x.isdelete = 0 AND t.prohibit_direction = 'N' ORDER BY userid LIMIT 0 , 15
10.6 >>> table , type , key , extra x index userid Using where y ref user_group_member_tab_fk_02 Using index z eq_ref primary Using where t ref prohibit_direction Using index condition
10.11 >>> table , type , key , extra t ref prohibit_direction Using index condition; Using temporary;Using filesort x all Using where; Using join buffer(flat, BNL join) y ref user_group_member_tab_fk_02 Using index z ref primary Using where
Answer
For what's changed, see Changes and Improvements in MariaDB 10.11. There was at least one optimization improvement, MDEV-28852. It's also worth questioning whether, although the data is the same, the statistics are the same. See This one trick can make MariaDB 30x faster and the linked articles.
