File: index_stat_auto.result

package info (click to toggle)
mysql-8.0 8.0.43-3
  • links: PTS, VCS
  • area: main
  • in suites: sid
  • size: 1,273,924 kB
  • sloc: cpp: 4,684,605; ansic: 412,450; pascal: 108,398; java: 83,641; perl: 30,221; cs: 27,067; sql: 26,594; sh: 24,181; python: 21,816; yacc: 17,169; php: 11,522; xml: 7,388; javascript: 7,076; makefile: 2,194; lex: 1,075; awk: 670; asm: 520; objc: 183; ruby: 97; lisp: 86
file content (371 lines) | stat: -rw-r--r-- 14,437 bytes parent folder | download
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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
create table t1 (
a1 int unsigned not null,
b1 int unsigned not null,
c1 int unsigned not null,
primary key (a1),
index b1x (b1),
index c1x (c1)
) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
create table t2 (
a2 int unsigned not null,
b2 int unsigned not null,
c2 int unsigned not null,
primary key (a2),
index b2x (b2),
index c2x (c2)
) engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
set @is_enable = 0;
set @is_enable = NULL;
# is_enable_on=0 is_enable_off=1
# ndb_index_stat_enable - before
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
set @@local.ndb_index_stat_enable = 0;
set @@global.ndb_index_stat_enable = 0;
# ndb_index_stat_enable - after
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
# is_enable_on=1 is_enable_off=0
# ndb_index_stat_enable - before
Variable_name	Value
ndb_index_stat_enable	OFF
Variable_name	Value
ndb_index_stat_enable	OFF
# ndb_index_stat_enable - after
Variable_name	Value
ndb_index_stat_enable	ON
Variable_name	Value
ndb_index_stat_enable	ON
Waiting for the index stats to be updated
Index stats updated
# is_enable_on=0 is_enable_off=1
# ndb_index_stat_enable - before
Variable_name	Value
ndb_index_stat_enable	ON
Variable_name	Value
ndb_index_stat_enable	ON
# ndb_index_stat_enable - after
Variable_name	Value
ndb_index_stat_enable	OFF
Variable_name	Value
ndb_index_stat_enable	OFF
set @is_enable = 1;
set @is_enable = NULL;
# is_enable_on=1 is_enable_off=0
# ndb_index_stat_enable - before
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
set @@global.ndb_index_stat_enable = 1;
set @@local.ndb_index_stat_enable = 1;
# ndb_index_stat_enable - after
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
Waiting for the index stats to be updated
Index stats updated
# must use b1x
explain select * from t1
where b1 = 5 and c1 = 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7	ref	b1x,c1x	b1x	4	const	#	#	Using pushed condition (`test`.`t1`.`c1` = 5)
Warnings:
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))
# must use c2x
explain select * from t2
where b2 = 5 and c2 = 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p0,p1,p2,p3,p4,p5,p6,p7	ref	b2x,c2x	c2x	4	const	#	#	Using pushed condition (`test`.`t2`.`b2` = 5)
Warnings:
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))
# must use b1x, c2x
explain select * from t1, t2
where c1 = c2 and b1 = 5 and b2 = 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7	ref	b1x,c1x	b1x	4	const	#	#	Parent of 2 pushed join@1
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)
Warnings:
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`))
# must use c2x, b1x
explain select * from t1, t2
where b1 = b2 and c1 = 5 and c2 = 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p0,p1,p2,p3,p4,p5,p6,p7	ref	b2x,c2x	c2x	4	const	#	#	Parent of 2 pushed join@1
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)
Warnings:
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`))
# must use t1, c2x
explain select * from t1, t2
where c1 = c2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7	ALL	c1x	NULL	NULL	NULL	#	#	Parent of 2 pushed join@1
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
Warnings:
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`)
# must use t2, b1x
explain select * from t1, t2
where b1 = b2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p0,p1,p2,p3,p4,p5,p6,p7	ALL	b2x	NULL	NULL	NULL	#	#	Parent of 2 pushed join@1
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
Warnings:
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`)
# should NOT say: Using index for group-by
explain select distinct (a1) from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7	ALL	PRIMARY	NULL	NULL	NULL	#	#	NULL
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`a1` AS `a1` from `test`.`t1`
# must say: Using index for group by
explain select distinct (b1) from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7	index	b1x	b1x	4	NULL	#	#	NULL
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`b1` AS `b1` from `test`.`t1`
# must say: Using index for group by
explain select distinct (c1) from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7	index	c1x	c1x	4	NULL	#	#	NULL
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`c1` AS `c1` from `test`.`t1`
drop table t1, t2;
create table t1 (a1 int, b1 int, primary key(b1), key(a1)) engine=ndbcluster partition by key() partitions 1;
create table t2 (b2 int, c2 int, primary key(b2,c2)) engine=ndbcluster partition by key() partitions 1;
set @is_enable = 0;
set @is_enable = NULL;
# is_enable_on=0 is_enable_off=1
# ndb_index_stat_enable - before
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
set @@local.ndb_index_stat_enable = 0;
set @@global.ndb_index_stat_enable = 0;
# ndb_index_stat_enable - after
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
set @is_enable = 1;
set @is_enable = NULL;
# is_enable_on=1 is_enable_off=0
# ndb_index_stat_enable - before
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
set @@global.ndb_index_stat_enable = 1;
set @@local.ndb_index_stat_enable = 1;
# ndb_index_stat_enable - after
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
Waiting for the index stats to be updated
Index stats updated
set @is_enable = 0;
set @is_enable = NULL;
# is_enable_on=0 is_enable_off=1
# ndb_index_stat_enable - before
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
set @@local.ndb_index_stat_enable = 0;
set @@global.ndb_index_stat_enable = 0;
# ndb_index_stat_enable - after
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
# table t1 is only for forcing record by key count for table t2 that should be near 50 (not 1)
set @is_enable = 1;
set @is_enable = NULL;
# is_enable_on=1 is_enable_off=0
# ndb_index_stat_enable - before
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
set @@global.ndb_index_stat_enable = 1;
set @@local.ndb_index_stat_enable = 1;
# ndb_index_stat_enable - after
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
Waiting for the index stats to be updated
Index stats updated
explain select * from t1, t2 where b2 = b1 and a1 = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0	ref	PRIMARY,a1	a1	5	const	#	100.00	Parent of 2 pushed join@1
1	SIMPLE	t2	p0	ref	PRIMARY	PRIMARY	4	test.t1.b1	#	100.00	Child of 't1' in pushed join@1
Warnings:
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`))
drop table t1, t2;
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;
set @is_enable = 0;
set @is_enable = NULL;
# is_enable_on=0 is_enable_off=1
# ndb_index_stat_enable - before
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
set @@local.ndb_index_stat_enable = 0;
set @@global.ndb_index_stat_enable = 0;
# ndb_index_stat_enable - after
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
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;
set @is_enable = 1;
set @is_enable = NULL;
# is_enable_on=1 is_enable_off=0
# ndb_index_stat_enable - before
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	OFF
set @@global.ndb_index_stat_enable = 1;
set @@local.ndb_index_stat_enable = 1;
# ndb_index_stat_enable - after
show global variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
show local variables like 'ndb_index_stat_enable';
Variable_name	Value
ndb_index_stat_enable	ON
Waiting for the index stats to be updated
Index stats updated
select i, count(*) from t1 group by 1 order by 1;
i	count(*)
0	256
1	256
2	256
3	256
4	256
5	256
6	256
7	256
8	256
9	256
select l, count(*) from t1 group by 1 order by 1;
l	count(*)
0	256
1	256
2	256
3	256
4	256
5	256
6	256
7	256
8	256
9	256
explain SELECT count(*) as Count FROM t1 WHERE I = 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0	ref	I	I	5	const	256	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select count(0) AS `Count` from `test`.`t1` where (`test`.`t1`.`I` = 5)
SELECT count(*) as Count FROM t1 WHERE I = 5;
Count
256
explain SELECT count(*) as Count FROM t1 WHERE L = 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0	ref	L	L	5	const	256	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select count(0) AS `Count` from `test`.`t1` where (`test`.`t1`.`L` = 5)
SELECT count(*) as Count FROM t1 WHERE L = 5;
Count
256
drop table t1;
#
# Bug#28714864: Creating a table with unsupported index length crashes the data nodes
#
#setup
create table t1(
a int auto_increment primary key,
b varchar(3053),
c varchar(3056),
d varchar(3072),
col1 varchar(762) not null,
col2 varchar(762) not null,
col3 varchar(762) not null,
col4 varchar(762) not null
) engine ndb charset latin1;
# populate enough rows to generate index stats
# create index on b - it has the maximum key size supported by ndb index stat
create index idxb on t1(b);
drop index idxb on t1;
# create index on c - it has a key size unsupported by ndb index stat
# a warning should be returned
create index idxc on t1(c);
Warnings:
Warning	1296	Specified key 'idxc' was too long (max = 3056 bytes); statistics will not be generated
# create index on d - it has the maximum key size allowed by mysql(3072)
# but the key size is unsupported by ndb index stat
# a warning should be returned
create index idxd on t1(d);
Warnings:
Warning	1296	Specified key 'idxd' was too long (max = 3056 bytes); statistics will not be generated
# create index on col1..col4 - it has the maximum key size supported by ndb index stat
create index idxcol on t1(col1, col2, col3, col4);
drop index idxcol on t1;
#cleanup
drop table t1;