MySQL Forums
Forum List  »  Optimizer & Parser

MYSQL5.7:OPTIMIZER Contradict Explain
Posted by: yijie fu
Date: September 07, 2022 05:02AM

MYSQL5.7:OPTIMIZER Contradict Explain
example1:
Index:ALTER TABLE t_app_recommended_info ADD INDEX idx_uid_create_time_type (uid,create_time,type);
SQL:select * from t_app_recommended_info where uid = 1252057754179211264 and create_time >'2022-09-01 00:00:00' and type =1 and params like '%"couponId":12582268875%' and params like '%"hmsr":"saomaqudao"%';
explain:key-len=12

OPTIMIZER_TRACE:refine_plan: "pushed_index_condition": "((`t_app_recommended_info`.`type` = 1) and (`t_app_recommended_info`.`uid` = 1252057754179211264) and (`t_app_recommended_info`.`create_time` > '2022-09-01 00:00:00'))",
example2:
Index:ALTER TABLE t_app_recommended_info ADD INDEX idx_uid_type_createtime (uid,type,create_time);
SQL:select * from t_app_recommended_info where uid = 1252057754179211264 and create_time >'2022-09-01 00:00:00' and type =1 and params like '%"couponId":12582268875%' and params like '%"hmsr":"saomaqudao"%';
explain:key-len=16

OPTIMIZER_TRACE:refine_plan: "pushed_index_condition": "((`t_app_recommended_info`.`type` = 1) and (`t_app_recommended_info`.`uid` = 1252057754179211264) and (`t_app_recommended_info`.`create_time` > '2022-09-01 00:00:00'))",
example1 compair example2 [explain key-len] find type(4byte)not used,but OPTIMIZER_TRACE:refine_plan: find user index' type,help.

Options: ReplyQuote


Subject
Views
Written By
Posted
MYSQL5.7:OPTIMIZER Contradict Explain
552
September 07, 2022 05:02AM
261
September 07, 2022 05:26AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.