Debian Codegraph @

mysql-test/suite/ndb_opt/index_stat_auto.test

raw
1 -- source include/have_ndb.inc
2
3 #
4 # This test is a subset of index_stat.test. All the ANALYZE TABLE
5 # statements have been removed and we instead wait for the stats
6 # to be automatically updated
7 #
8
9 create table t1 (
10 a1 int unsigned not null,
11 b1 int unsigned not null,
12 c1 int unsigned not null,
13 primary key (a1),
14 index b1x (b1),
15 index c1x (c1)
16 ) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
17
18 create table t2 (
19 a2 int unsigned not null,
20 b2 int unsigned not null,
21 c2 int unsigned not null,
22 primary key (a2),
23 index b2x (b2),
24 index c2x (c2)
25 ) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
26
27 # Disable index stats at the handler level so that it can be
28 # reasonably ascertained that the latest stats are in the
29 # cache later. This protects us from using stale stats and thus
30 # leading to non-deterministic EXPLAIN output
31 set @is_enable = 0;
32 --source index_stat_enable.inc
33 --let $old_event_count=query_get_value(SHOW STATUS LIKE 'Ndb_index_stat_event_count', Value, 1)
34
35 # enough rows to make index stats more approximate
36
37 --disable_query_log
38 let $i = 1000;
39 while ($i)
40 {
41 dec $i;
42 eval insert into t1 values ($i, $i % 100, $i % 10);
43 }
44
45 set @is_enable = 1;
46 --source index_stat_enable.inc
47 --let $index_stat_db=test
48 --let $index_stat_table=t1
49 --source wait_index_stat_cache_updated.inc
50 set @is_enable = 0;
51 --source index_stat_enable.inc
52 --let $old_event_count=query_get_value(SHOW STATUS LIKE 'Ndb_index_stat_event_count', Value, 1)
53
54 let $i = 1000;
55 while ($i)
56 {
57 dec $i;
58 eval insert into t2 values ($i, $i % 10, $i % 100);
59 }
60 --enable_query_log
61
62 set @is_enable = 1;
63 --source index_stat_enable.inc
64 --let $index_stat_db=test
65 --let $index_stat_table=t2
66 --source wait_index_stat_cache_updated.inc
67
68 # TEST: key equal constant
69
70 --echo # must use b1x
71 --replace_column 10 # 11 #
72 explain select * from t1
73 where b1 = 5 and c1 = 5;
74
75 --echo # must use c2x
76 --replace_column 10 # 11 #
77 explain select * from t2
78 where b2 = 5 and c2 = 5;
79
80 # TEST: keys equal constant in join
81
82 --echo # must use b1x, c2x
83 --replace_column 10 # 11 #
84 explain select * from t1, t2
85 where c1 = c2 and b1 = 5 and b2 = 5;
86
87 --echo # must use c2x, b1x
88 --replace_column 10 # 11 #
89 explain select * from t1, t2
90 where b1 = b2 and c1 = 5 and c2 = 5;
91
92 # TEST: join via keys of different selectivity
93
94 --echo # must use t1, c2x
95 --replace_column 10 # 11 #
96 explain select * from t1, t2
97 where c1 = c2;
98 --echo # must use t2, b1x
99 --replace_column 10 # 11 #
100 explain select * from t1, t2
101 where b1 = b2;
102
103 # TEST: bug#44760 quick distinct
104 # QUICK_GROUP_MIN_MAX_SELECT says "Using index for group-by".
105 # Should happen only for low cardinality index.
106 # wl4124_todo: result is wrong until HA_KEYREAD_ONLY is set
107
108 --echo # should NOT say: Using index for group-by
109 --replace_column 10 # 11 #
110 explain select distinct (a1) from t1;
111
112 --echo # must say: Using index for group by
113 --replace_column 10 # 11 #
114 explain select distinct (b1) from t1;
115
116 --echo # must say: Using index for group by
117 --replace_column 10 # 11 #
118 explain select distinct (c1) from t1;
119
120 # TEST: end
121 drop table t1, t2;
122
123 #
124 # Check estimates of records per key for partial keys using unique/primary ordered index
125 #
126
127 create table t1 (a1 int, b1 int, primary key(b1), key(a1)) engine=ndbcluster partition by key() partitions 1;
128 create table t2 (b2 int, c2 int, primary key(b2,c2)) engine=ndbcluster partition by key() partitions 1;
129 set @is_enable = 0;
130 --source index_stat_enable.inc
131 --let $old_event_count=query_get_value(SHOW STATUS LIKE 'Ndb_index_stat_event_count', Value, 1)
132 --disable_query_log
133 let $i = 100;
134 while ($i)
135 {
136 eval insert into t1 (a1,b1) values ($i,$i);
137 dec $i;
138 }
139 --enable_query_log
140
141 set @is_enable = 1;
142 --source index_stat_enable.inc
143 --let $index_stat_db=test
144 --let $index_stat_table=t1
145 --source wait_index_stat_cache_updated.inc
146
147 set @is_enable = 0;
148 --source index_stat_enable.inc
149 --let $old_event_count=query_get_value(SHOW STATUS LIKE 'Ndb_index_stat_event_count', Value, 1)
150 --disable_query_log
151 let $i = 100;
152 while ($i)
153 {
154 eval insert into t2 (b2,c2) values ($i mod 2, $i div 2);
155 dec $i;
156 }
157 --enable_query_log
158
159 --echo # table t1 is only for forcing record by key count for table t2 that should be near 50 (not 1)
160 set @is_enable = 1;
161 --source index_stat_enable.inc
162 --let $index_stat_db=test
163 --let $index_stat_table=t2
164 --source wait_index_stat_cache_updated.inc
165 # Hide Extra column
166 --replace_column 10 #
167 explain select * from t1, t2 where b2 = b1 and a1 = 1;
168
169 drop table t1, t2;
170
171 # bugXXXXX rule b3.3 fix
172
173 CREATE TABLE t1(
174 K INT NOT NULL AUTO_INCREMENT,
175 I INT,
176 J INT,
177 L INT,
178 PRIMARY KEY(K),
179 KEY(I,J),
180 KEY(L)
181 ) ENGINE=ndbcluster
182 partition by key (K) partitions 1;
183
184 set @is_enable = 0;
185 --source index_stat_enable.inc
186 --let $old_event_count=query_get_value(SHOW STATUS LIKE 'Ndb_index_stat_event_count', Value, 1)
187
188 INSERT INTO t1(I,J,L) VALUES
189 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
190 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(0,0,0);
191
192 INSERT INTO t1(I,J,L) SELECT I,1,I FROM t1;
193 INSERT INTO t1(I,J,L) SELECT I,2,I FROM t1;
194 INSERT INTO t1(I,J,L) SELECT I,3,I FROM t1;
195 INSERT INTO t1(I,J,L) SELECT I,4,I FROM t1;
196 INSERT INTO t1(I,J,L) SELECT I,5,I FROM t1;
197 INSERT INTO t1(I,J,L) SELECT I,6,I FROM t1;
198 INSERT INTO t1(I,J,L) SELECT I,7,I FROM t1;
199 INSERT INTO t1(I,J,L) SELECT I,8,I FROM t1;
200
201 set @is_enable = 1;
202 --source index_stat_enable.inc
203 --let $index_stat_db=test
204 --let $index_stat_table=t1
205 --source wait_index_stat_cache_updated.inc
206
207 select i, count(*) from t1 group by 1 order by 1;
208 select l, count(*) from t1 group by 1 order by 1;
209
210 explain SELECT count(*) as Count FROM t1 WHERE I = 5;
211 SELECT count(*) as Count FROM t1 WHERE I = 5;
212
213 # before fix rows was 128 instead of 256
214 explain SELECT count(*) as Count FROM t1 WHERE L = 5;
215 SELECT count(*) as Count FROM t1 WHERE L = 5;
216
217 drop table t1;
218
219 --echo #
220 --echo # Bug#28714864: Creating a table with unsupported index length crashes the data nodes
221 --echo #
222
223 --echo #setup
224 create table t1(
225 a int auto_increment primary key,
226 b varchar(3053),
227 c varchar(3056),
228 d varchar(3072),
229 col1 varchar(762) not null,
230 col2 varchar(762) not null,
231 col3 varchar(762) not null,
232 col4 varchar(762) not null
233 ) engine ndb charset latin1;
234
235 --echo # populate enough rows to generate index stats
236 --disable_query_log
237 insert into t1 (b, c, d, col1, col2, col3, col4)
238 values (repeat('a', 3051), repeat('b', 3056), repeat('c', 3072),
239 repeat('d', 762), repeat('e', 762), repeat('f', 762), repeat('g', 762));
240 insert into t1 (b, c, d, col1, col2, col3, col4)
241 values (repeat('h', 3051), repeat('i', 3056), repeat('j', 3072),
242 repeat('k', 762), repeat('l', 762), repeat('m', 762), repeat('n', 762));
243 let $i = 5;
244 while ($i)
245 {
246 eval insert into t1 (b, c, d, col1, col2, col3, col4)
247 select b, c, d, col1, col2, col3, col4 from t1;
248 dec $i;
249 }
250 --enable_query_log
251
252 --echo # create index on b - it has the maximum key size supported by ndb index stat
253 create index idxb on t1(b); # max key size = 3056 (col size = 3053, length bytes = 2, nullmask = 1)
254
255 drop index idxb on t1;
256
257 --echo # create index on c - it has a key size unsupported by ndb index stat
258 --echo # a warning should be returned
259 create index idxc on t1(c); # max key size = 3059 (col size = 3056, length bytes = 2, nullmask = 1)
260
261 --echo # create index on d - it has the maximum key size allowed by mysql(3072)
262 --echo # but the key size is unsupported by ndb index stat
263 --echo # a warning should be returned
264 create index idxd on t1(d); # max key size = 3075 (col size = 3072, length bytes = 2, nullmask = 1)
265
266 --echo # create index on col1..col4 - it has the maximum key size supported by ndb index stat
267 # max key size = 3056 (total col size = 3048[762 * 4], length bytes = 8[2 * 4], nullmask = 0)
268 create index idxcol on t1(col1, col2, col3, col4);
269
270 drop index idxcol on t1;
271
272 --echo #cleanup
273 drop table t1;