The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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 Answered by Ian Gilfillan in this comment.

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.

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.