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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
-- source include/have_ndb.inc
#
# This is a duplicate of the index_stat_partitions.test. All the ANALYZE
# TABLE statements have been removed and we instead wait for the stats
# to be automatically updated
#
create table record_in_range(no int primary key auto_increment,
query varchar(256), part_count int,
explain_rows int, count_rows int) engine = innodb;
CREATE TABLE t1(
K INT NOT NULL AUTO_INCREMENT,
I INT,
J INT,
L INT,
PRIMARY KEY(K),
KEY(I,J),
KEY(L)
) ENGINE=ndbcluster
partition by key (K) partitions 1;
--let $old_event_count=query_get_value(SHOW STATUS LIKE 'Ndb_index_stat_event_count', Value, 1)
set @is_enable = 0;
--source index_stat_enable.inc
INSERT INTO t1(I,J,L) VALUES
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(0,0,0);
INSERT INTO t1(I,J,L) SELECT I,1,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,2,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,3,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,4,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,5,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,6,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,7,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,8,I FROM t1;
INSERT INTO t1(I,J,L) SELECT I,9,I FROM t1;
set @is_enable = 1;
--source index_stat_enable.inc
--let $index_stat_db=test
--let $index_stat_name=t1
--source wait_index_stat_cache_updated.inc
select i, count(*) from t1 group by 1 order by 1;
select l, count(*) from t1 group by 1 order by 1;
--disable_query_log
let $i = 10;
let $p = 1;
while ($i)
{
--let $q=SELECT count(*) as Count FROM t1 WHERE i = $i
--let $explain_type= query_get_value(EXPLAIN $q, type, 1)
--let $explain_rows= query_get_value(EXPLAIN $q, rows, 1)
--let $count_rows= query_get_value($q, Count, 1)
--eval insert into record_in_range(query,part_count,explain_rows,count_rows) values ('$q',$p,$explain_rows,$count_rows);
dec $i;
}
let $l = 10;
while ($l)
{
--let $q=SELECT count(*) as Count FROM t1 WHERE l = $l
--let $explain_type= query_get_value(EXPLAIN $q, type, 1)
--let $explain_rows= query_get_value(EXPLAIN $q, rows, 1)
--let $count_rows= query_get_value($q, Count, 1)
--eval insert into record_in_range(query,part_count,explain_rows,count_rows) values ('$q',$p,$explain_rows,$count_rows);
dec $l;
}
#--let $old_sample_version=$sample_version
--let $old_event_count=query_get_value(SHOW STATUS LIKE 'Ndb_index_stat_event_count', Value, 1)
set @is_enable = 0;
--source index_stat_enable.inc
alter table t1 partition by key() partitions 8;
--enable_query_log
set @is_enable = 1;
--source index_stat_enable.inc
--let $index_stat_db=test
--let $index_stat_name=t1
--source wait_index_stat_cache_updated.inc
--disable_query_log
let $p = 8;
let $i = 10;
while ($i)
{
--let $q=SELECT count(*) as Count FROM t1 WHERE i = $i
--let $explain_type= query_get_value(EXPLAIN $q, type, 1)
--let $explain_rows= query_get_value(EXPLAIN $q, rows, 1)
--let $count_rows= query_get_value($q, Count, 1)
--eval insert into record_in_range(query,part_count,explain_rows,count_rows) values ('$q',$p,$explain_rows,$count_rows);
dec $i;
}
let $l = 10;
while ($l)
{
--let $q=SELECT count(*) as Count FROM t1 WHERE l = $l
--let $explain_type= query_get_value(EXPLAIN $q, type, 1)
--let $explain_rows= query_get_value(EXPLAIN $q, rows, 1)
--let $count_rows= query_get_value($q, Count, 1)
--eval insert into record_in_range(query,part_count,explain_rows,count_rows) values ('$q',$p,$explain_rows,$count_rows);
dec $l;
}
--enable_query_log
# Change sql_mode to allow the reporting query below
# which uses "DISTINCT with an ORDER BY on column which
# is not in the select list"
--source include/turn_off_only_full_group_by.inc
select distinct
substring(rr.query from INSTR(rr.query, 'WHERE')) as 'WHERE',
count_rows,
p1.val as p1_val,
if(p8.err_pct is not null and abs(p8.err_pct)<45,'-',p8.val) p8_val,
case when p1.err_pct is null then '-' when abs(p1.err_pct)<45 then '<45' else p1.err_pct end p1_err_pct,
case when p8.err_pct is null then '-' when abs(p8.err_pct)<45 then '<45' else p8.err_pct end p8_err_pct
from record_in_range rr,
(select query, explain_rows as val, if(count_rows>0,floor(100*(explain_rows - count_rows) / count_rows),NULL) err_pct
from record_in_range p where part_count = 1) p1,
(select query, explain_rows as val, if(count_rows>0,floor(100*(explain_rows - count_rows) / count_rows),NULL) err_pct
from record_in_range p where part_count = 8) p8
where rr.query = p1.query
and rr.query = p8.query
order by rr.no;
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
DROP TABLE t1, record_in_range;
|
