mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
241 lines
10 KiB
Text
241 lines
10 KiB
Text
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 variable '@@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 variable '@@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 variable '@@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 variable '@@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 variable '@@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 variable '@@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 variable '@@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 variable '@@optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
|