what is difference 10.6 and 10.11 version.
You are viewing an old version of this question. View
the current version here.
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.
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.