1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
--source include/have_sequence.inc
--source include/not_embedded.inc
--source include/have_innodb.inc
#
# Show the costs for rowid filter
#
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;
--disable_ps_protocol
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;
set @trace=(select trace from information_schema.optimizer_trace);
select json_detailed(json_extract(@trace, '$**.considered_access_paths'));
--echo
--echo The following trace should have a different rowid_filter_key cost
--echo
set statement optimizer_adjust_secondary_key_costs=2 for
explain select * from t1 where nm like '500%' AND fl2 = 0;
set @trace=(select trace from information_schema.optimizer_trace);
select json_detailed(json_extract(@trace, '$**.considered_access_paths'));
--enable_ps_protocol
drop table t1, name, flag2;
select @@optimizer_adjust_secondary_key_costs;
set @@optimizer_adjust_secondary_key_costs=7;
select @@optimizer_adjust_secondary_key_costs;
set @@optimizer_adjust_secondary_key_costs=default;
--echo #
--echo # MDEV-34664: fix_innodb_cardinality
--echo #
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;
select count(distinct b),count(distinct b,c), count(distinct b,c,d) from t1;
show index from t1;
explain select * from t1,t2 where t1.b=t2.a;
set @@optimizer_adjust_secondary_key_costs=8;
explain select * from t1,t2 where t1.b=t2.a;
show index from t1;
# Flush tables or show index is needed to refresh the data in table share
flush tables;
explain select * from t1,t2 where t1.b=t2.a;
show index from t1;
# Check that the option does not affect other usage
connect (user2, localhost, root,,);
show index from t1;
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;
|
