Quelle  secondary_key_costs.result   Sprache: Lisp

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 charset=latin1;
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 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations 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 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations 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 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations 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 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations 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 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations 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 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release