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 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;