Debian Codegraph @

mysql-test/suite/ndb_opt/index_stat_auto.result

raw
1 create table t1 (
2 a1 int unsigned not null,
3 b1 int unsigned not null,
4 c1 int unsigned not null,
5 primary key (a1),
6 index b1x (b1),
7 index c1x (c1)
8 ) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
9 create table t2 (
10 a2 int unsigned not null,
11 b2 int unsigned not null,
12 c2 int unsigned not null,
13 primary key (a2),
14 index b2x (b2),
15 index c2x (c2)
16 ) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
17 set @is_enable = 0;
18 set @is_enable = NULL;
19 # is_enable_on=0 is_enable_off=1
20 # ndb_index_stat_enable - before
21 show global variables like 'ndb_index_stat_enable';
22 Variable_name Value
23 ndb_index_stat_enable ON
24 show local variables like 'ndb_index_stat_enable';
25 Variable_name Value
26 ndb_index_stat_enable ON
27 set @@local.ndb_index_stat_enable = 0;
28 set @@global.ndb_index_stat_enable = 0;
29 # ndb_index_stat_enable - after
30 show global variables like 'ndb_index_stat_enable';
31 Variable_name Value
32 ndb_index_stat_enable OFF
33 show local variables like 'ndb_index_stat_enable';
34 Variable_name Value
35 ndb_index_stat_enable OFF
36 # is_enable_on=1 is_enable_off=0
37 # ndb_index_stat_enable - before
38 Variable_name Value
39 ndb_index_stat_enable OFF
40 Variable_name Value
41 ndb_index_stat_enable OFF
42 # ndb_index_stat_enable - after
43 Variable_name Value
44 ndb_index_stat_enable ON
45 Variable_name Value
46 ndb_index_stat_enable ON
47 Waiting for the index stats to be updated
48 Index stats updated
49 # is_enable_on=0 is_enable_off=1
50 # ndb_index_stat_enable - before
51 Variable_name Value
52 ndb_index_stat_enable ON
53 Variable_name Value
54 ndb_index_stat_enable ON
55 # ndb_index_stat_enable - after
56 Variable_name Value
57 ndb_index_stat_enable OFF
58 Variable_name Value
59 ndb_index_stat_enable OFF
60 set @is_enable = 1;
61 set @is_enable = NULL;
62 # is_enable_on=1 is_enable_off=0
63 # ndb_index_stat_enable - before
64 show global variables like 'ndb_index_stat_enable';
65 Variable_name Value
66 ndb_index_stat_enable OFF
67 show local variables like 'ndb_index_stat_enable';
68 Variable_name Value
69 ndb_index_stat_enable OFF
70 set @@global.ndb_index_stat_enable = 1;
71 set @@local.ndb_index_stat_enable = 1;
72 # ndb_index_stat_enable - after
73 show global variables like 'ndb_index_stat_enable';
74 Variable_name Value
75 ndb_index_stat_enable ON
76 show local variables like 'ndb_index_stat_enable';
77 Variable_name Value
78 ndb_index_stat_enable ON
79 Waiting for the index stats to be updated
80 Index stats updated
81 # must use b1x
82 explain select * from t1
83 where b1 = 5 and c1 = 5;
84 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
85 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x,c1x b1x 4 const # # Using pushed condition (`test`.`t1`.`c1` = 5)
86 Warnings:
87 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c1` = 5) and (`test`.`t1`.`b1` = 5))
88 # must use c2x
89 explain select * from t2
90 where b2 = 5 and c2 = 5;
91 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
92 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref b2x,c2x c2x 4 const # # Using pushed condition (`test`.`t2`.`b2` = 5)
93 Warnings:
94 Note 1003 /* select#1 */ select `test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t2` where ((`test`.`t2`.`c2` = 5) and (`test`.`t2`.`b2` = 5))
95 # must use b1x, c2x
96 explain select * from t1, t2
97 where c1 = c2 and b1 = 5 and b2 = 5;
98 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
99 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x,c1x b1x 4 const # # Parent of 2 pushed join@1
100 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref b2x,c2x c2x 4 test.t1.c1 # # Child of 't1' in pushed join@1; Using pushed condition (`test`.`t2`.`b2` = 5)
101 Warnings:
102 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b2` = 5) and (`test`.`t1`.`b1` = 5) and (`test`.`t2`.`c2` = `test`.`t1`.`c1`))
103 # must use c2x, b1x
104 explain select * from t1, t2
105 where b1 = b2 and c1 = 5 and c2 = 5;
106 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
107 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref b2x,c2x c2x 4 const # # Parent of 2 pushed join@1
108 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x,c1x b1x 4 test.t2.b2 # # Child of 't2' in pushed join@1; Using pushed condition (`test`.`t1`.`c1` = 5)
109 Warnings:
110 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c2` = 5) and (`test`.`t1`.`c1` = 5) and (`test`.`t1`.`b1` = `test`.`t2`.`b2`))
111 # must use t1, c2x
112 explain select * from t1, t2
113 where c1 = c2;
114 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
115 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL c1x NULL NULL NULL # # Parent of 2 pushed join@1
116 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref c2x c2x 4 test.t1.c1 # # Child of 't1' in pushed join@1
117 Warnings:
118 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`c2` = `test`.`t1`.`c1`)
119 # must use t2, b1x
120 explain select * from t1, t2
121 where b1 = b2;
122 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
123 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL b2x NULL NULL NULL # # Parent of 2 pushed join@1
124 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x b1x 4 test.t2.b2 # # Child of 't2' in pushed join@1
125 Warnings:
126 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b1` = `test`.`t2`.`b2`)
127 # should NOT say: Using index for group-by
128 explain select distinct (a1) from t1;
129 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
130 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL # # NULL
131 Warnings:
132 Note 1003 /* select#1 */ select distinct `test`.`t1`.`a1` AS `a1` from `test`.`t1`
133 # must say: Using index for group by
134 explain select distinct (b1) from t1;
135 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
136 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index b1x b1x 4 NULL # # NULL
137 Warnings:
138 Note 1003 /* select#1 */ select distinct `test`.`t1`.`b1` AS `b1` from `test`.`t1`
139 # must say: Using index for group by
140 explain select distinct (c1) from t1;
141 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
142 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index c1x c1x 4 NULL # # NULL
143 Warnings:
144 Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1`
145 drop table t1, t2;
146 create table t1 (a1 int, b1 int, primary key(b1), key(a1)) engine=ndbcluster partition by key() partitions 1;
147 create table t2 (b2 int, c2 int, primary key(b2,c2)) engine=ndbcluster partition by key() partitions 1;
148 set @is_enable = 0;
149 set @is_enable = NULL;
150 # is_enable_on=0 is_enable_off=1
151 # ndb_index_stat_enable - before
152 show global variables like 'ndb_index_stat_enable';
153 Variable_name Value
154 ndb_index_stat_enable ON
155 show local variables like 'ndb_index_stat_enable';
156 Variable_name Value
157 ndb_index_stat_enable ON
158 set @@local.ndb_index_stat_enable = 0;
159 set @@global.ndb_index_stat_enable = 0;
160 # ndb_index_stat_enable - after
161 show global variables like 'ndb_index_stat_enable';
162 Variable_name Value
163 ndb_index_stat_enable OFF
164 show local variables like 'ndb_index_stat_enable';
165 Variable_name Value
166 ndb_index_stat_enable OFF
167 set @is_enable = 1;
168 set @is_enable = NULL;
169 # is_enable_on=1 is_enable_off=0
170 # ndb_index_stat_enable - before
171 show global variables like 'ndb_index_stat_enable';
172 Variable_name Value
173 ndb_index_stat_enable OFF
174 show local variables like 'ndb_index_stat_enable';
175 Variable_name Value
176 ndb_index_stat_enable OFF
177 set @@global.ndb_index_stat_enable = 1;
178 set @@local.ndb_index_stat_enable = 1;
179 # ndb_index_stat_enable - after
180 show global variables like 'ndb_index_stat_enable';
181 Variable_name Value
182 ndb_index_stat_enable ON
183 show local variables like 'ndb_index_stat_enable';
184 Variable_name Value
185 ndb_index_stat_enable ON
186 Waiting for the index stats to be updated
187 Index stats updated
188 set @is_enable = 0;
189 set @is_enable = NULL;
190 # is_enable_on=0 is_enable_off=1
191 # ndb_index_stat_enable - before
192 show global variables like 'ndb_index_stat_enable';
193 Variable_name Value
194 ndb_index_stat_enable ON
195 show local variables like 'ndb_index_stat_enable';
196 Variable_name Value
197 ndb_index_stat_enable ON
198 set @@local.ndb_index_stat_enable = 0;
199 set @@global.ndb_index_stat_enable = 0;
200 # ndb_index_stat_enable - after
201 show global variables like 'ndb_index_stat_enable';
202 Variable_name Value
203 ndb_index_stat_enable OFF
204 show local variables like 'ndb_index_stat_enable';
205 Variable_name Value
206 ndb_index_stat_enable OFF
207 # table t1 is only for forcing record by key count for table t2 that should be near 50 (not 1)
208 set @is_enable = 1;
209 set @is_enable = NULL;
210 # is_enable_on=1 is_enable_off=0
211 # ndb_index_stat_enable - before
212 show global variables like 'ndb_index_stat_enable';
213 Variable_name Value
214 ndb_index_stat_enable OFF
215 show local variables like 'ndb_index_stat_enable';
216 Variable_name Value
217 ndb_index_stat_enable OFF
218 set @@global.ndb_index_stat_enable = 1;
219 set @@local.ndb_index_stat_enable = 1;
220 # ndb_index_stat_enable - after
221 show global variables like 'ndb_index_stat_enable';
222 Variable_name Value
223 ndb_index_stat_enable ON
224 show local variables like 'ndb_index_stat_enable';
225 Variable_name Value
226 ndb_index_stat_enable ON
227 Waiting for the index stats to be updated
228 Index stats updated
229 explain select * from t1, t2 where b2 = b1 and a1 = 1;
230 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
231 1 SIMPLE t1 p0 ref PRIMARY,a1 a1 5 const # 100.00 Parent of 2 pushed join@1
232 1 SIMPLE t2 p0 ref PRIMARY PRIMARY 4 test.t1.b1 # 100.00 Child of 't1' in pushed join@1
233 Warnings:
234 Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a1` = 1) and (`test`.`t2`.`b2` = `test`.`t1`.`b1`))
235 drop table t1, t2;
236 CREATE TABLE t1(
237 K INT NOT NULL AUTO_INCREMENT,
238 I INT,
239 J INT,
240 L INT,
241 PRIMARY KEY(K),
242 KEY(I,J),
243 KEY(L)
244 ) ENGINE=ndbcluster
245 partition by key (K) partitions 1;
246 set @is_enable = 0;
247 set @is_enable = NULL;
248 # is_enable_on=0 is_enable_off=1
249 # ndb_index_stat_enable - before
250 show global variables like 'ndb_index_stat_enable';
251 Variable_name Value
252 ndb_index_stat_enable ON
253 show local variables like 'ndb_index_stat_enable';
254 Variable_name Value
255 ndb_index_stat_enable ON
256 set @@local.ndb_index_stat_enable = 0;
257 set @@global.ndb_index_stat_enable = 0;
258 # ndb_index_stat_enable - after
259 show global variables like 'ndb_index_stat_enable';
260 Variable_name Value
261 ndb_index_stat_enable OFF
262 show local variables like 'ndb_index_stat_enable';
263 Variable_name Value
264 ndb_index_stat_enable OFF
265 INSERT INTO t1(I,J,L) VALUES
266 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
267 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(0,0,0);
268 INSERT INTO t1(I,J,L) SELECT I,1,I FROM t1;
269 INSERT INTO t1(I,J,L) SELECT I,2,I FROM t1;
270 INSERT INTO t1(I,J,L) SELECT I,3,I FROM t1;
271 INSERT INTO t1(I,J,L) SELECT I,4,I FROM t1;
272 INSERT INTO t1(I,J,L) SELECT I,5,I FROM t1;
273 INSERT INTO t1(I,J,L) SELECT I,6,I FROM t1;
274 INSERT INTO t1(I,J,L) SELECT I,7,I FROM t1;
275 INSERT INTO t1(I,J,L) SELECT I,8,I FROM t1;
276 set @is_enable = 1;
277 set @is_enable = NULL;
278 # is_enable_on=1 is_enable_off=0
279 # ndb_index_stat_enable - before
280 show global variables like 'ndb_index_stat_enable';
281 Variable_name Value
282 ndb_index_stat_enable OFF
283 show local variables like 'ndb_index_stat_enable';
284 Variable_name Value
285 ndb_index_stat_enable OFF
286 set @@global.ndb_index_stat_enable = 1;
287 set @@local.ndb_index_stat_enable = 1;
288 # ndb_index_stat_enable - after
289 show global variables like 'ndb_index_stat_enable';
290 Variable_name Value
291 ndb_index_stat_enable ON
292 show local variables like 'ndb_index_stat_enable';
293 Variable_name Value
294 ndb_index_stat_enable ON
295 Waiting for the index stats to be updated
296 Index stats updated
297 select i, count(*) from t1 group by 1 order by 1;
298 i count(*)
299 0 256
300 1 256
301 2 256
302 3 256
303 4 256
304 5 256
305 6 256
306 7 256
307 8 256
308 9 256
309 select l, count(*) from t1 group by 1 order by 1;
310 l count(*)
311 0 256
312 1 256
313 2 256
314 3 256
315 4 256
316 5 256
317 6 256
318 7 256
319 8 256
320 9 256
321 explain SELECT count(*) as Count FROM t1 WHERE I = 5;
322 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
323 1 SIMPLE t1 p0 ref I I 5 const 256 100.00 NULL
324 Warnings:
325 Note 1003 /* select#1 */ select count(0) AS `Count` from `test`.`t1` where (`test`.`t1`.`I` = 5)
326 SELECT count(*) as Count FROM t1 WHERE I = 5;
327 Count
328 256
329 explain SELECT count(*) as Count FROM t1 WHERE L = 5;
330 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
331 1 SIMPLE t1 p0 ref L L 5 const 256 100.00 NULL
332 Warnings:
333 Note 1003 /* select#1 */ select count(0) AS `Count` from `test`.`t1` where (`test`.`t1`.`L` = 5)
334 SELECT count(*) as Count FROM t1 WHERE L = 5;
335 Count
336 256
337 drop table t1;
338 #
339 # Bug#28714864: Creating a table with unsupported index length crashes the data nodes
340 #
341 #setup
342 create table t1(
343 a int auto_increment primary key,
344 b varchar(3053),
345 c varchar(3056),
346 d varchar(3072),
347 col1 varchar(762) not null,
348 col2 varchar(762) not null,
349 col3 varchar(762) not null,
350 col4 varchar(762) not null
351 ) engine ndb charset latin1;
352 # populate enough rows to generate index stats
353 # create index on b - it has the maximum key size supported by ndb index stat
354 create index idxb on t1(b);
355 drop index idxb on t1;
356 # create index on c - it has a key size unsupported by ndb index stat
357 # a warning should be returned
358 create index idxc on t1(c);
359 Warnings:
360 Warning 1296 Specified key 'idxc' was too long (max = 3056 bytes); statistics will not be generated
361 # create index on d - it has the maximum key size allowed by mysql(3072)
362 # but the key size is unsupported by ndb index stat
363 # a warning should be returned
364 create index idxd on t1(d);
365 Warnings:
366 Warning 1296 Specified key 'idxd' was too long (max = 3056 bytes); statistics will not be generated
367 # create index on col1..col4 - it has the maximum key size supported by ndb index stat
368 create index idxcol on t1(col1, col2, col3, col4);
369 drop index idxcol on t1;
370 #cleanup
371 drop table t1;