create table t1 ( pk int primary key auto_increment, nm varchar(32), fl1 tinyint default 0, fl2 tinyint default 0, index idx1(nm, fl1), index idx2(fl2) ) engine=myisam; create table name ( pk int primary key auto_increment, nm bigint ) engine=myisam; create table flag2 ( pk int primary key auto_increment, fl2 tinyint ) engine=myisam; insert into name(nm) select seq from seq_1_to_1000 order by rand(17); insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19); insert into t1(nm,fl2) select nm, fl2 from name, flag2 where name.pk = flag2.pk; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date set optimizer_trace="enabled=on"; set optimizer_switch='rowid_filter=on'; set statement optimizer_adjust_secondary_key_costs=0 for explain select * from t1 where nm like '500%' AND fl2 = 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1,idx2 idx1 35 NULL 1 Using index condition; Using where Warnings: Warning 1287 '@@optimizer_adjust_secondary_key_costs' is deprecated and will be removed in a future release Warning 1287 '@@optimizer_adjust_secondary_key_costs' is deprecated and will be removed in a future release set @trace=(select trace from information_schema.optimizer_trace); select json_detailed(json_extract(@trace, '$**.considered_access_paths')); json_detailed(json_extract(@trace, '$**.considered_access_paths')) [ [ { "access_type": "ref", "index": "idx2", "used_range_estimates": true, "filter": { "rowid_filter_index": "idx1", "index_only_cost": 0.045598762, "filter_startup_cost": 0.000899465, "find_key_and_filter_lookup_cost": 0.03086808, "filter_selectivity": 0.001, "original_rows": 492, "new_rows": 0.492, "original_access_cost": 0.59235049, "with_filter_access_cost": 0.077013594, "original_found_rows_cost": 0.546751728, "with_filter_found_rows_cost": 5.467517e-4, "org_cost": 0.60809449, "filter_cost": 0.077928803, "filter_used": true }, "rows": 0.492, "cost": 0.077928803, "chosen": true }, { "filter": { "rowid_filter_index": "idx2", "index_only_cost": 0.000881127, "filter_startup_cost": 0.066293508, "find_key_and_filter_lookup_cost": 8.646449e-5, "filter_selectivity": 0.492, "original_rows": 1, "new_rows": 0.492, "original_access_cost": 0.001992411, "with_filter_access_cost": 0.001514343, "original_found_rows_cost": 0.001111284, "with_filter_found_rows_cost": 5.467517e-4, "org_cost": 0.002024411, "filter_cost": 0.067823595, "filter_used": false }, "access_type": "range", "range_index": "idx1", "rows": 1, "rows_after_filter": 1, "rows_out": 0.492, "cost": 0.002574553, "chosen": true } ] ] The following trace should have a different rowid_filter_key cost set statement optimizer_adjust_secondary_key_costs=2 for explain select * from t1 where nm like '500%' AND fl2 = 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1,idx2 idx1 35 NULL 1 Using index condition; Using where Warnings: Warning 1287 '@@optimizer_adjust_secondary_key_costs' is deprecated and will be removed in a future release Warning 1287 '@@optimizer_adjust_secondary_key_costs' is deprecated and will be removed in a future release set @trace=(select trace from information_schema.optimizer_trace); select json_detailed(json_extract(@trace, '$**.considered_access_paths')); json_detailed(json_extract(@trace, '$**.considered_access_paths')) [ [ { "access_type": "ref", "index": "idx2", "used_range_estimates": true, "filter": { "rowid_filter_index": "idx1", "index_only_cost": 0.045598762, "filter_startup_cost": 0.000899465, "find_key_and_filter_lookup_cost": 0.03086808, "filter_selectivity": 0.001, "original_rows": 492, "new_rows": 0.492, "original_access_cost": 0.59235049, "with_filter_access_cost": 0.077013594, "original_found_rows_cost": 0.546751728, "with_filter_found_rows_cost": 5.467517e-4, "org_cost": 0.60809449, "filter_cost": 0.077928803, "filter_used": true }, "rows": 0.492, "cost": 0.077928803, "chosen": true }, { "filter": { "rowid_filter_index": "idx2", "index_only_cost": 0.000881127, "filter_startup_cost": 0.066293508, "find_key_and_filter_lookup_cost": 8.646449e-5, "filter_selectivity": 0.492, "original_rows": 1, "new_rows": 0.492, "original_access_cost": 0.001992411, "with_filter_access_cost": 0.001514343, "original_found_rows_cost": 0.001111284, "with_filter_found_rows_cost": 5.467517e-4, "org_cost": 0.002024411, "filter_cost": 0.067823595, "filter_used": false }, "access_type": "range", "range_index": "idx1", "rows": 1, "rows_after_filter": 1, "rows_out": 0.492, "cost": 0.002574553, "chosen": true } ] ] drop table t1, name, flag2; select @@optimizer_adjust_secondary_key_costs; @@optimizer_adjust_secondary_key_costs 0 set @@optimizer_adjust_secondary_key_costs=7; Warnings: Warning 1287 '@@optimizer_adjust_secondary_key_costs' is deprecated and will be removed in a future release Warning 1292 Truncated incorrect optimizer_adjust_secondary_ke... value: '7' select @@optimizer_adjust_secondary_key_costs; @@optimizer_adjust_secondary_key_costs 2 set @@optimizer_adjust_secondary_key_costs=default; Warnings: Warning 1287 '@@optimizer_adjust_secondary_key_costs' is deprecated and will be removed in a future release # # MDEV-34664: fix_innodb_cardinality # set @save_userstat=@@global.userstat; set @save_ispsp=@@global.innodb_stats_persistent_sample_pages; set @@global.innodb_stats_persistent_sample_pages=20; set @@global.userstat=on; set use_stat_tables=PREFERABLY_FOR_QUERIES; create or replace table t1 (a int primary key, b int, c int, d int, key(b,c,d)) engine=innodb; insert into t1 select seq,seq/100,seq/60,seq/10 from seq_1_to_1000; create or replace table t2 (a int); insert into t2 values (1),(2),(3); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK select count(distinct b),count(distinct b,c), count(distinct b,c,d) from t1; count(distinct b) count(distinct b,c) count(distinct b,c,d) 11 25 125 show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 PRIMARY 1 a A 1000 NULL NULL BTREE NO t1 1 b 1 b A 11 NULL NULL YES BTREE NO t1 1 b 2 c A 25 NULL NULL YES BTREE NO t1 1 b 3 d A 125 NULL NULL YES BTREE NO explain select * from t1,t2 where t1.b=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 ref b b 5 test.t2.a 90 Using index set @@optimizer_adjust_secondary_key_costs=8; Warnings: Warning 1287 '@@optimizer_adjust_secondary_key_costs' is deprecated and will be removed in a future release Warning 1292 Truncated incorrect optimizer_adjust_secondary_ke... value: '8' explain select * from t1,t2 where t1.b=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 ref b b 5 test.t2.a 90 Using index show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 PRIMARY 1 a A 1000 NULL NULL BTREE NO t1 1 b 1 b A 11 NULL NULL YES BTREE NO t1 1 b 2 c A 25 NULL NULL YES BTREE NO t1 1 b 3 d A 125 NULL NULL YES BTREE NO flush tables; explain select * from t1,t2 where t1.b=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 ref b b 5 test.t2.a 90 Using index show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 PRIMARY 1 a A 1000 NULL NULL BTREE NO t1 1 b 1 b A 11 NULL NULL YES BTREE NO t1 1 b 2 c A 25 NULL NULL YES BTREE NO t1 1 b 3 d A 125 NULL NULL YES BTREE NO connect user2, localhost, root,,; show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 PRIMARY 1 a A 1000 NULL NULL BTREE NO t1 1 b 1 b A 11 NULL NULL YES BTREE NO t1 1 b 2 c A 25 NULL NULL YES BTREE NO t1 1 b 3 d A 125 NULL NULL YES BTREE NO connection default; disconnect user2; drop table t1,t2; set global userstat=@save_userstat; set global innodb_stats_persistent_sample_pages=@save_ispsp; set @@optimizer_adjust_secondary_key_costs=default; Warnings: Warning 1287 '@@optimizer_adjust_secondary_key_costs' is deprecated and will be removed in a future release