Debian Codegraph @

mysql-test/suite/ndb_opt/index_stat.result

raw
1 set @is_enable_default = @@global.ndb_index_stat_enable;
2 set @is_enable = 1;
3 set @is_enable = NULL;
4 # is_enable_on=0 is_enable_off=0
5 # ndb_index_stat_enable - before
6 show global variables like 'ndb_index_stat_enable';
7 Variable_name Value
8 ndb_index_stat_enable ON
9 show local variables like 'ndb_index_stat_enable';
10 Variable_name Value
11 ndb_index_stat_enable ON
12 # ndb_index_stat_enable - after
13 show global variables like 'ndb_index_stat_enable';
14 Variable_name Value
15 ndb_index_stat_enable ON
16 show local variables like 'ndb_index_stat_enable';
17 Variable_name Value
18 ndb_index_stat_enable ON
19 show global variables like 'ndb_index_stat_option';
20 Variable_name Value
21 ndb_index_stat_option loop_enable=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,check_delay=10m,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90,zero_total=0
22 set @save_option = @@global.ndb_index_stat_option;
23 set @@global.ndb_index_stat_option = 'loop_idle=3333,cache_limit=44M';
24 set @@global.ndb_index_stat_option = 'cache_lowpct=85,evict_delay=55';
25 set @@global.ndb_index_stat_option = 'check_delay=234s';
26 show global variables like 'ndb_index_stat_option';
27 Variable_name Value
28 ndb_index_stat_option loop_enable=1000ms,loop_idle=3333ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,check_delay=234s,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=55s,cache_limit=44M,cache_lowpct=85,zero_total=0
29 set @@global.ndb_index_stat_option = @save_option;
30 show global variables like 'ndb_index_stat_option';
31 Variable_name Value
32 ndb_index_stat_option loop_enable=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,check_delay=10m,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90,zero_total=0
33 create table t1 (
34 a1 int unsigned not null,
35 b1 int unsigned not null,
36 c1 int unsigned not null,
37 primary key (a1),
38 index b1x (b1),
39 index c1x (c1)
40 ) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
41 create table t2 (
42 a2 int unsigned not null,
43 b2 int unsigned not null,
44 c2 int unsigned not null,
45 primary key (a2),
46 index b2x (b2),
47 index c2x (c2)
48 ) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
49 analyze table t1, t2;
50 Table Op Msg_type Msg_text
51 test.t1 analyze status OK
52 test.t2 analyze status OK
53 # must use b1x
54 explain select * from t1
55 where b1 = 5 and c1 = 5;
56 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
57 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x,c1x b1x 4 const # # Using pushed condition (`test`.`t1`.`c1` = 5)
58 Warnings:
59 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))
60 # must use c2x
61 explain select * from t2
62 where b2 = 5 and c2 = 5;
63 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
64 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref b2x,c2x c2x 4 const # # Using pushed condition (`test`.`t2`.`b2` = 5)
65 Warnings:
66 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))
67 # must use b1x, c2x
68 explain select * from t1, t2
69 where c1 = c2 and b1 = 5 and b2 = 5;
70 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
71 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref b1x,c1x b1x 4 const # # Parent of 2 pushed join@1
72 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)
73 Warnings:
74 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`))
75 # must use c2x, b1x
76 explain select * from t1, t2
77 where b1 = b2 and c1 = 5 and c2 = 5;
78 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
79 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ref b2x,c2x c2x 4 const # # Parent of 2 pushed join@1
80 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)
81 Warnings:
82 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`))
83 # must use t1, c2x
84 explain select * from t1, t2
85 where c1 = c2;
86 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
87 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL c1x NULL NULL NULL # # Parent of 2 pushed join@1
88 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
89 Warnings:
90 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`)
91 # must use t2, b1x
92 explain select * from t1, t2
93 where b1 = b2;
94 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
95 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL b2x NULL NULL NULL # # Parent of 2 pushed join@1
96 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
97 Warnings:
98 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`)
99 # should NOT say: Using index for group-by
100 explain select distinct (a1) from t1;
101 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
102 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL PRIMARY NULL NULL NULL # # NULL
103 Warnings:
104 Note 1003 /* select#1 */ select distinct `test`.`t1`.`a1` AS `a1` from `test`.`t1`
105 # must say: Using index for group by
106 explain select distinct (b1) from t1;
107 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
108 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index b1x b1x 4 NULL # # NULL
109 Warnings:
110 Note 1003 /* select#1 */ select distinct `test`.`t1`.`b1` AS `b1` from `test`.`t1`
111 # must say: Using index for group by
112 explain select distinct (c1) from t1;
113 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
114 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 index c1x c1x 4 NULL # # NULL
115 Warnings:
116 Note 1003 /* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1`
117 drop table t1, t2;
118 create table t1 (a int, b int, c varchar(10) not null,
119 primary key using hash (a), index(b,c)) engine=ndb;
120 insert into t1 values
121 (1,10,'aaa'),(2,10,'bbb'),(3,10,'ccc'),
122 (4,20,'aaa'),(5,20,'bbb'),(6,20,'ccc'),
123 (7,30,'aaa'),(8,30,'bbb'),(9,30,'ccc');
124 analyze table t1;
125 Table Op Msg_type Msg_text
126 test.t1 analyze status OK
127 set @@local.ndb_index_stat_enable = 0;
128 select count(*) from t1 where b < 10;
129 count(*)
130 0
131 select count(*) from t1 where b >= 10 and c >= 'bbb';
132 count(*)
133 6
134 select count(*) from t1 where b > 10;
135 count(*)
136 6
137 select count(*) from t1 where b <= 20 and c < 'ccc';
138 count(*)
139 4
140 select count(*) from t1 where b = 20 and c = 'ccc';
141 count(*)
142 1
143 select count(*) from t1 where b > 20;
144 count(*)
145 3
146 select count(*) from t1 where b = 30 and c > 'aaa';
147 count(*)
148 2
149 select count(*) from t1 where b <= 20;
150 count(*)
151 6
152 select count(*) from t1 where b >= 20 and c > 'aaa';
153 count(*)
154 4
155 set @@local.ndb_index_stat_enable = 1;
156 select count(*) from t1 where b < 10;
157 count(*)
158 0
159 select count(*) from t1 where b >= 10 and c >= 'bbb';
160 count(*)
161 6
162 select count(*) from t1 where b > 10;
163 count(*)
164 6
165 select count(*) from t1 where b <= 20 and c < 'ccc';
166 count(*)
167 4
168 select count(*) from t1 where b = 20 and c = 'ccc';
169 count(*)
170 1
171 select count(*) from t1 where b > 20;
172 count(*)
173 3
174 select count(*) from t1 where b = 30 and c > 'aaa';
175 count(*)
176 2
177 select count(*) from t1 where b <= 20;
178 count(*)
179 6
180 select count(*) from t1 where b >= 20 and c > 'aaa';
181 count(*)
182 4
183 set @@local.ndb_index_stat_enable = 0;
184 select count(*) from t1 where b < 10;
185 count(*)
186 0
187 select count(*) from t1 where b >= 10 and c >= 'bbb';
188 count(*)
189 6
190 select count(*) from t1 where b > 10;
191 count(*)
192 6
193 select count(*) from t1 where b <= 20 and c < 'ccc';
194 count(*)
195 4
196 select count(*) from t1 where b = 20 and c = 'ccc';
197 count(*)
198 1
199 select count(*) from t1 where b > 20;
200 count(*)
201 3
202 select count(*) from t1 where b = 30 and c > 'aaa';
203 count(*)
204 2
205 select count(*) from t1 where b <= 20;
206 count(*)
207 6
208 select count(*) from t1 where b >= 20 and c > 'aaa';
209 count(*)
210 4
211 set @@local.ndb_index_stat_enable = 1;
212 select count(*) from t1 where b < 10;
213 count(*)
214 0
215 select count(*) from t1 where b >= 10 and c >= 'bbb';
216 count(*)
217 6
218 select count(*) from t1 where b > 10;
219 count(*)
220 6
221 select count(*) from t1 where b <= 20 and c < 'ccc';
222 count(*)
223 4
224 select count(*) from t1 where b = 20 and c = 'ccc';
225 count(*)
226 1
227 select count(*) from t1 where b > 20;
228 count(*)
229 3
230 select count(*) from t1 where b = 30 and c > 'aaa';
231 count(*)
232 2
233 select count(*) from t1 where b <= 20;
234 count(*)
235 6
236 select count(*) from t1 where b >= 20 and c > 'aaa';
237 count(*)
238 4
239 drop table t1;
240 create table t1 (a int, b int, primary key using hash (a), index x1 (b))
241 engine=ndb;
242 insert into t1 values (1,11),(2,22),(3,33);
243 select * from t1 order by a;
244 a b
245 1 11
246 2 22
247 3 33
248 select * from t1 order by a;
249 a b
250 1 11
251 2 22
252 3 33
253 analyze table t1;
254 Table Op Msg_type Msg_text
255 test.t1 analyze status OK
256 drop table t1;
257 create table t1 (
258 pk int not null,
259 a tinyint not null,
260 b tinyint unsigned not null,
261 c smallint not null,
262 d smallint unsigned not null,
263 e mediumint not null,
264 f mediumint unsigned not null,
265 g int not null,
266 h int unsigned not null,
267 i bigint not null,
268 j bigint unsigned not null,
269 k float not null,
270 l double not null,
271 m decimal not null,
272 n decimal unsigned not null,
273 primary key using hash (pk),
274 index (a),
275 index (b),
276 index (c),
277 index (d),
278 index (e),
279 index (f),
280 index (g),
281 index (h),
282 index (i),
283 index (j),
284 index (k),
285 index (l),
286 index (m),
287 index (n)
288 ) engine=ndb;
289 Warnings:
290 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
291 insert into t1 values
292 (1,11,11,11,11,11,11,11,11,11,11,11,11,11,11),
293 (2,22,22,22,22,22,22,22,22,22,22,22,22,22,22),
294 (3,33,33,33,33,33,33,33,33,33,33,33,33,33,33);
295 select count(*) from t1 where a > 22;
296 count(*)
297 1
298 select count(*) from t1 where b > 22;
299 count(*)
300 1
301 select count(*) from t1 where c > 22;
302 count(*)
303 1
304 select count(*) from t1 where d > 22;
305 count(*)
306 1
307 select count(*) from t1 where e > 22;
308 count(*)
309 1
310 select count(*) from t1 where f > 22;
311 count(*)
312 1
313 select count(*) from t1 where g > 22;
314 count(*)
315 1
316 select count(*) from t1 where h > 22;
317 count(*)
318 1
319 select count(*) from t1 where i > 22;
320 count(*)
321 1
322 select count(*) from t1 where j > 22;
323 count(*)
324 1
325 select count(*) from t1 where k > 22;
326 count(*)
327 1
328 select count(*) from t1 where l > 22;
329 count(*)
330 1
331 select count(*) from t1 where m > 22;
332 count(*)
333 1
334 select count(*) from t1 where n > 22;
335 count(*)
336 1
337 analyze table t1;
338 Table Op Msg_type Msg_text
339 test.t1 analyze status OK
340 select count(*) from t1 where a > 22;
341 count(*)
342 1
343 select count(*) from t1 where b > 22;
344 count(*)
345 1
346 select count(*) from t1 where c > 22;
347 count(*)
348 1
349 select count(*) from t1 where d > 22;
350 count(*)
351 1
352 select count(*) from t1 where e > 22;
353 count(*)
354 1
355 select count(*) from t1 where f > 22;
356 count(*)
357 1
358 select count(*) from t1 where g > 22;
359 count(*)
360 1
361 select count(*) from t1 where h > 22;
362 count(*)
363 1
364 select count(*) from t1 where i > 22;
365 count(*)
366 1
367 select count(*) from t1 where j > 22;
368 count(*)
369 1
370 select count(*) from t1 where k > 22;
371 count(*)
372 1
373 select count(*) from t1 where l > 22;
374 count(*)
375 1
376 select count(*) from t1 where m > 22;
377 count(*)
378 1
379 select count(*) from t1 where n > 22;
380 count(*)
381 1
382 drop table t1;
383 create table t1 (
384 pk int not null,
385 a datetime not null,
386 b date not null,
387 c year not null,
388 d time not null,
389 e timestamp not null,
390 primary key using hash (pk),
391 index (a),
392 index (b),
393 index (c),
394 index (d),
395 index (e)
396 ) engine=ndb;
397 insert into t1 values
398 (1,'1971-01-01 01:01:01','1971-01-01','1971','01:01:01','1971-01-01 01:01:01'),
399 (2,'1972-02-02 02:02:02','1972-02-02','1972','02:02:02','1972-02-02 02:02:02'),
400 (3,'1973-03-03 03:03:03','1973-03-03','1973','03:03:03','1973-03-03 03:03:03');
401 select count(*) from t1 where a > '1972-02-02 02:02:02';
402 count(*)
403 1
404 select count(*) from t1 where b > '1972-02-02';
405 count(*)
406 1
407 select count(*) from t1 where c > '1972';
408 count(*)
409 1
410 select count(*) from t1 where d > '02:02:02';
411 count(*)
412 1
413 select count(*) from t1 where e > '1972-02-02 02:02:02';
414 count(*)
415 1
416 analyze table t1;
417 Table Op Msg_type Msg_text
418 test.t1 analyze status OK
419 select count(*) from t1 where a > '1972-02-02 02:02:02';
420 count(*)
421 1
422 select count(*) from t1 where b > '1972-02-02';
423 count(*)
424 1
425 select count(*) from t1 where c > '1972';
426 count(*)
427 1
428 select count(*) from t1 where d > '02:02:02';
429 count(*)
430 1
431 select count(*) from t1 where e > '1972-02-02 02:02:02';
432 count(*)
433 1
434 drop table t1;
435 create table t1 (
436 pk int not null,
437 a char(10) not null,
438 b varchar(10) not null,
439 c varchar(1000) not null,
440 d binary(10) not null,
441 e varbinary(10) not null,
442 f varbinary(1000) not null,
443 primary key using hash (pk),
444 index (a),
445 index (b),
446 index (c),
447 index (d),
448 index (e),
449 index (f)
450 ) engine=ndb character set latin1;
451 insert into t1 values
452 ('1','111','111','111','111','111','111'),
453 ('2','222','222','222','222','222','222'),
454 ('3','333','333','333','333','333','333');
455 select count(*) from t1 where a > '222';
456 count(*)
457 1
458 select count(*) from t1 where b > '222';
459 count(*)
460 1
461 select count(*) from t1 where c > '222';
462 count(*)
463 1
464 select count(*) from t1 where d > '222';
465 count(*)
466 2
467 select count(*) from t1 where e > '222';
468 count(*)
469 1
470 select count(*) from t1 where f > '222';
471 count(*)
472 1
473 analyze table t1;
474 Table Op Msg_type Msg_text
475 test.t1 analyze status OK
476 select count(*) from t1 where a > '222';
477 count(*)
478 1
479 select count(*) from t1 where b > '222';
480 count(*)
481 1
482 select count(*) from t1 where c > '222';
483 count(*)
484 1
485 select count(*) from t1 where d > '222';
486 count(*)
487 2
488 select count(*) from t1 where e > '222';
489 count(*)
490 1
491 select count(*) from t1 where f > '222';
492 count(*)
493 1
494 drop table t1;
495 create table t1 (a1 int, b1 int, primary key(b1), key(a1)) engine=ndbcluster partition by key() partitions 1;
496 create table t2 (b2 int, c2 int, primary key(b2,c2)) engine=ndbcluster partition by key() partitions 1;
497 # table t1 is only for forcing record by key count for table t2 that should be near 50 (not 1)
498 analyze table t1, t2;
499 Table Op Msg_type Msg_text
500 test.t1 analyze status OK
501 test.t2 analyze status OK
502 explain select * from t1, t2 where b2 = b1 and a1 = 1;
503 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
504 1 SIMPLE t1 p0 ref PRIMARY,a1 a1 5 const # 100.00 Parent of 2 pushed join@1
505 1 SIMPLE t2 p0 ref PRIMARY PRIMARY 4 test.t1.b1 # 100.00 Child of 't1' in pushed join@1
506 Warnings:
507 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`))
508 drop table t1, t2;
509 CREATE TABLE t1(
510 K INT NOT NULL AUTO_INCREMENT,
511 I INT,
512 J INT,
513 L INT,
514 PRIMARY KEY(K),
515 KEY(I,J),
516 KEY(L)
517 ) ENGINE=ndbcluster
518 partition by key (K) partitions 1;
519 INSERT INTO t1(I,J,L) VALUES
520 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
521 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(0,0,0);
522 INSERT INTO t1(I,J,L) SELECT I,1,I FROM t1;
523 INSERT INTO t1(I,J,L) SELECT I,2,I FROM t1;
524 INSERT INTO t1(I,J,L) SELECT I,3,I FROM t1;
525 INSERT INTO t1(I,J,L) SELECT I,4,I FROM t1;
526 INSERT INTO t1(I,J,L) SELECT I,5,I FROM t1;
527 INSERT INTO t1(I,J,L) SELECT I,6,I FROM t1;
528 INSERT INTO t1(I,J,L) SELECT I,7,I FROM t1;
529 INSERT INTO t1(I,J,L) SELECT I,8,I FROM t1;
530 select i, count(*) from t1 group by 1 order by 1;
531 i count(*)
532 0 256
533 1 256
534 2 256
535 3 256
536 4 256
537 5 256
538 6 256
539 7 256
540 8 256
541 9 256
542 select l, count(*) from t1 group by 1 order by 1;
543 l count(*)
544 0 256
545 1 256
546 2 256
547 3 256
548 4 256
549 5 256
550 6 256
551 7 256
552 8 256
553 9 256
554 ANALYZE TABLE t1;
555 Table Op Msg_type Msg_text
556 test.t1 analyze status OK
557 explain SELECT count(*) as Count FROM t1 WHERE I = 5;
558 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
559 1 SIMPLE t1 p0 ref I I 5 const 256 100.00 NULL
560 Warnings:
561 Note 1003 /* select#1 */ select count(0) AS `Count` from `test`.`t1` where (`test`.`t1`.`I` = 5)
562 SELECT count(*) as Count FROM t1 WHERE I = 5;
563 Count
564 256
565 explain SELECT count(*) as Count FROM t1 WHERE L = 5;
566 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
567 1 SIMPLE t1 p0 ref L L 5 const 256 100.00 NULL
568 Warnings:
569 Note 1003 /* select#1 */ select count(0) AS `Count` from `test`.`t1` where (`test`.`t1`.`L` = 5)
570 SELECT count(*) as Count FROM t1 WHERE L = 5;
571 Count
572 256
573 drop table t1;
574 create table t1 (
575 a int unsigned not null,
576 b char(180) not null,
577 primary key using hash (a),
578 index (b)
579 ) engine=ndb charset=binary;
580 insert into t1 values (1,'a'),(2,'b'),(3,'c');
581 analyze table t1;
582 Table Op Msg_type Msg_text
583 test.t1 analyze status OK
584 analyze table t1;
585 Table Op Msg_type Msg_text
586 test.t1 analyze status OK
587 analyze table t1;
588 Table Op Msg_type Msg_text
589 test.t1 analyze status OK
590 drop table t1;
591 CREATE TABLE t1 (id int unsigned NOT NULL auto_increment PRIMARY KEY,
592 val int unsigned, UNIQUE (val)) ENGINE=NDBCluster;
593 INSERT INTO test.t1 values (1,1), (2,2);
594 DROP TABLE t1;
595 CREATE TABLE t1 (
596 a INT PRIMARY KEY,
597 b INT,
598 c INT,
599 UNIQUE(c)
600 ) ENGINE NDB PARTITION BY KEY(a);
601 Insert 100 rows
602 ANALYZE TABLE t1;
603 Table Op Msg_type Msg_text
604 test.t1 analyze status OK
605 SELECT CASE COUNT(*) > 0
606 WHEN 1 THEN 'YES' ELSE 'NO' END
607 AS stats_metadata_present FROM ndbinfo.index_stats;
608 stats_metadata_present
609 YES
610 21-or-27 records in table ndb_index_stat_sample
611 ALTER TABLE t1 COMMENT="NDB_TABLE=READ_BACKUP=0";
612 ANALYZE TABLE t1;
613 Table Op Msg_type Msg_text
614 test.t1 analyze status OK
615 SELECT COUNT(index_id)
616 FROM ndbinfo.index_stats
617 GROUP BY index_id
618 HAVING COUNT(index_id) > 1;
619 COUNT(index_id)
620 2
621 2
622 21-or-27 records in table ndb_index_stat_sample
623 EXPLAIN is the same both pre- and post-ALTER
624 DROP TABLE t1;
625 SELECT CASE COUNT(*) > 0
626 WHEN 1 THEN 'YES' ELSE 'NO' END
627 AS stats_metadata_present FROM ndbinfo.index_stats;
628 stats_metadata_present
629 YES
630 0 records in table ndb_index_stat_sample
631 call mtr.add_suppression("Failed to acquire global schema lock");
632 call mtr.add_suppression("Failed to release global schema lock");
633 set @is_enable = @is_enable_default;
634 set @is_enable = NULL;
635 # is_enable_on=0 is_enable_off=0
636 # ndb_index_stat_enable - before
637 show global variables like 'ndb_index_stat_enable';
638 Variable_name Value
639 ndb_index_stat_enable ON
640 show local variables like 'ndb_index_stat_enable';
641 Variable_name Value
642 ndb_index_stat_enable ON
643 # ndb_index_stat_enable - after
644 show global variables like 'ndb_index_stat_enable';
645 Variable_name Value
646 ndb_index_stat_enable ON
647 show local variables like 'ndb_index_stat_enable';
648 Variable_name Value
649 ndb_index_stat_enable ON