what is difference 10.6 and 10.11 version. - Source

Revision: 143699
User: 효중 김
Date: 2024-10-01 10:14

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?

<<code>>
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
<</code>>

Answer

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.

what is difference 10.6 and 10.11 version. - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB