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
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
|
set @save_rand_seed1= @@RAND_SEED1;
set @save_rand_seed2= @@RAND_SEED2;
set @@RAND_SEED1=810763568, @@RAND_SEED2=600681772;
create table t1(a int);
insert into t1 select seq from seq_1_to_10000 order by rand(),seq;
#
# parameters:
# mean mean for the column to be considered
# max_val max_value for the column to be considered
#
# This function generate a sample of a normal distribution
# This function return a point
# of the normal distribution with a given mean.
#
CREATE FUNCTION
generate_normal_distribution_sample(mean DOUBLE, max_val DOUBLE)RETURNS DOUBLE
BEGIN
DECLARE z DOUBLE DEFAULT 0;
SET z= (rand() + rand() + rand() + rand() + rand() + rand())/6;
SET z= 2*(max_val-mean)*z;
SET z= z + mean - (max_val-mean);
return z;
END|
#
# parameters:
# len length of the random string to be generated
#
# This function generates a random string for the length passed
# as an argument with characters in the range of [A,Z]
#
CREATE FUNCTION generate_random_string(len INT) RETURNS varchar(128)
BEGIN
DECLARE str VARCHAR(256) DEFAULT '';
DECLARE x INT DEFAULT 0;
WHILE (len > 0) DO
SET x =round(rand()*25);
SET str= CONCAT(str, CHAR(65 + x));
SET len= len-1;
END WHILE;
RETURN str;
END|
#
# parameters:
# mean mean for the column to be considered
# min_val min_value for the column to be considered
# max_val max_value for the column to be considered
#
# This function generate a normal distribution sample in the range of
# [min_val, max_val]
#
CREATE FUNCTION
clipped_normal_distribution(mean DOUBLE, min_val DOUBLE, max_val DOUBLE)
RETURNS INT
BEGIN
DECLARE r DOUBLE DEFAULT 0;
WHILE 1=1 DO
set r= generate_normal_distribution_sample(mean, max_val);
IF (r >= min_val AND r <= max_val) THEN
RETURN round(r);
end if;
END WHILE;
RETURN 0;
END|
create table t2 (id INT NOT NULL, a INT, b int);
insert into t2
select a, clipped_normal_distribution(12, 0, 64),
clipped_normal_distribution(32, 0, 128)
from t1;
CREATE TABLE t3(
id INT NOT NULL,
names VARCHAR(64),
address VARCHAR(128),
PRIMARY KEY (id)
);
#
# table t3 stores random strings calculated from the length stored in
# table t2
#
insert into t3
select id, generate_random_string(a), generate_random_string(b) from t2;
#
# All records fit in memory
#
set sort_buffer_size=262144*10;
analyze format=json select id DIV 100 as x,
MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
FROM t3
GROUP BY x;
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"read_sorted_file": {
"r_rows": 10000,
"filesort": {
"sort_key": "t3.`id` DIV 100",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"r_used_priority_queue": false,
"r_output_rows": 10000,
"r_buffer_size": "REPLACED",
"r_sort_mode": "sort_key,packed_addon_fields",
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 10000,
"r_rows": 10000,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"filtered": 100,
"r_filtered": 100
}
}
}
}
}
flush status;
select id DIV 100 as x,
MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
FROM t3
GROUP BY x;
x MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
0 3d24cb0237caf81aa74a2dddf367ac23
1 618f9b8b6cefaa268dcb5477eece5e90
2 fbfe93cc7713f852852f66e578d999aa
3 34802712af9c95664f41e789d19fb6e7
4 2d9a4d628013b45cea5301143ec559c5
5 324b8e682ad2233498f36979fddddf30
6 31ccc1ab69c00dcade71f7fda2fad599
7 e059e0279a5654a1c5f6596d220691bf
8 a21e5021cfb19be280f7fa2c2e70608e
9 6230b389a5bb0f9cd464b839e8824d0d
10 f2db66b6c4ad49e406f965a80ff5d224
11 75f39127d968cad28d30707889191d81
12 2e75191c0f32966a9e7675d0ddcf1c1c
13 808b4ecabb9cbb40ff3549c81b21d2f5
14 c7c0c0bbf0ab7558793a86eaca714a3f
15 3065d81035dc9c596bc235ab553f92de
16 13b1b17e93649fe49d6da356c2072c63
17 e3ce0af10608e6467ba4d38b135ff873
18 0f172b310ad772e13630b647e09ef79a
19 26bbfb77888fa151215fe8ff75e448e7
20 fcd6d5113e8178766dc370b470eb053a
21 d3860a17be6da02e17432f2ff80a261f
22 1d0cb7cd3f54c15bd41491e6fa449f12
23 15f95be2574a94677fa7e76ead23e025
24 924abdbb21c79cc7fd11d4d5b87ac339
25 7493978704b5223a4f5dc6d8beb51d90
26 f4ac4696b05577ba4a05334cd131cf94
27 cfd45d4915f342978fb6982a825f6cc2
28 2bd8981c6245c32ea1dc27ef394df94e
29 0e47838fe4be239939424fcef1289002
30 18e291b42da10b04434bdad010a5b66c
31 cbd1bf2554378ef7edd9087d4a8562d4
32 ddb1e7282bb637f7fe3aa3aed149b482
33 1963f2400c8870d67f99c391bc5040c2
34 d2b44d3c13a7a2c83de840892a370f47
35 7ff20517acf5ea901757262eae1edf5e
36 685fbfc47faad4243944d7331a2dee36
37 9d58b82b5f9d369a16c4f2bcc424b412
38 be15aa080dda65c9489892c34a330c30
39 4b52ddf7b599c9a00d094008a464c18c
40 441b03789e58aec883138d9c657a005e
41 e76cc7b1cadcb4f31e0232a0a8458620
42 3581a040fbf210f6942fe591621f0e41
43 acc552d3cba6a900054093c3c6876fc5
44 fd7fa7a0a4a58485a1aa30e804f024af
45 56afedd4f2e8101fbb4402051e9857e8
46 019e4178eccbdd84150f196668e40888
47 132c8e314dd7c9064e1cb4980856531c
48 dcc08184bf2f7cfb640af0c603ff7506
49 691b29183f64480003f8a1a74cd3ac13
50 105da23f1b4a63d755414beca9df20d5
51 30b2e973a0186721f9a7720be6be09e5
52 b307d14c06fd6f27173d683a5596b542
53 2125a788f107a4ba6a364984f7145156
54 aa0793982ae401cd2c1bea949d726ae1
55 e1797ba6b7ecf5f84cda2703f79c878c
56 78fc3f94127c0d5c6b3fa82ac3a8c167
57 b8c6d1e26cfd94aade5e32cf89e05da6
58 1e3481d6ff3b22f6ad739b93afe4c341
59 59e9d5be4a87ee707578a3a8e5d624e9
60 7a00e357826e950ffbe688c3f9a99a97
61 e5a8c0d404f6fb59d9e3dd88b13ba5a7
62 19d22c20fbf76b5b9ac746679ade26e7
63 c96f912f54b9b862532ce959398c76ca
64 86c167171b777a150356f1f083203d24
65 e1bca8d6cc1904a919cce1090e62f34d
66 f5dd3f6267376875239222dbefac87b1
67 d6a2aa26124dd42a9df9fa21b7eb8bce
68 ecd36ba9a56f89d4bf6fa83c2b800985
69 87b64475fd3f4db6dd68a45fb9a825dc
70 f5f7aa5569558b3e6556c72c9dfc429b
71 9e1cc67e0c13d00c771b6bed2fd566cf
72 3a6e7c6ce76a04fa2503d094121d84d5
73 5fd8ba2dd8d36814d06a1d278002e437
74 10db883700dde7289eb640df3a6d6676
75 3ebd59ed119d6dfda3eedc5bf05fd425
76 5008d3ce585f3b2dda2b313c57ebcb0a
77 ac7cee33e39827de02acedb7584acae5
78 3f440facefbd95f905942c75bfe60bbb
79 89639b3145c988c775d9d997c9d356ff
80 a6f8b29a83b2305188c241f2724ea998
81 d79f9bd47505cee69627e8044f7745c2
82 5f8d8b55bc9d5b44f817ada0b9bab2f7
83 411a99ef2e2af45640f0224274979fb5
84 60706cd1eb7dffae3d7a34133c5cb67e
85 272a5d09070b5a0569fb18e144100f8b
86 0f969fd16dd186e9eb099213a82fb119
87 6c48f63ec50e26ba42d8ca074daee9a6
88 83512539cc5a2561426564590d307a58
89 c57cf49bbeaa12aaba2106278681c9cd
90 d248507181561f0aba09182c4326fb11
91 a1e153dd8bb1334bccd73890fafc5ba9
92 d7a0ba2d1241a07abf3691074cecff76
93 466034794d577c41a9716bc9eb903ee2
94 b5e2158feb4290b04a7bdfdec0a17976
95 8a16a27008d033dd6e9074b042021e60
96 ccaa12e89f6de3058b3a2baddc4eff00
97 7fdbf02f369546d0fde2b7db85752e33
98 63b286bb777298397f3acf03fc3e9deb
99 b271f751a4952f86b002b9730bd02c8b
100 cbd5cef546acc0bfa33117d7c1ae58a8
show status like '%sort%';
Variable_name Value
Sort_merge_passes 0
Sort_priority_queue_sorts 0
Sort_range 0
Sort_rows 10000
Sort_scan 1
set sort_buffer_size=default;
#
# Test for merge_many_buff
#
set sort_buffer_size=32768;
analyze format=json select id DIV 100 as x,
MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
FROM t3
GROUP BY x;
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"read_sorted_file": {
"r_rows": 10000,
"filesort": {
"sort_key": "t3.`id` DIV 100",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"r_used_priority_queue": false,
"r_output_rows": 10000,
"r_sort_passes": 4,
"r_buffer_size": "REPLACED",
"r_sort_mode": "sort_key,packed_addon_fields",
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 10000,
"r_rows": 10000,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"filtered": 100,
"r_filtered": 100
}
}
}
}
}
flush status;
select id DIV 100 as x,
MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
FROM t3
GROUP BY x;
x MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
0 3d24cb0237caf81aa74a2dddf367ac23
1 618f9b8b6cefaa268dcb5477eece5e90
2 fbfe93cc7713f852852f66e578d999aa
3 34802712af9c95664f41e789d19fb6e7
4 2d9a4d628013b45cea5301143ec559c5
5 324b8e682ad2233498f36979fddddf30
6 31ccc1ab69c00dcade71f7fda2fad599
7 e059e0279a5654a1c5f6596d220691bf
8 a21e5021cfb19be280f7fa2c2e70608e
9 6230b389a5bb0f9cd464b839e8824d0d
10 f2db66b6c4ad49e406f965a80ff5d224
11 75f39127d968cad28d30707889191d81
12 2e75191c0f32966a9e7675d0ddcf1c1c
13 808b4ecabb9cbb40ff3549c81b21d2f5
14 c7c0c0bbf0ab7558793a86eaca714a3f
15 3065d81035dc9c596bc235ab553f92de
16 13b1b17e93649fe49d6da356c2072c63
17 e3ce0af10608e6467ba4d38b135ff873
18 0f172b310ad772e13630b647e09ef79a
19 26bbfb77888fa151215fe8ff75e448e7
20 fcd6d5113e8178766dc370b470eb053a
21 d3860a17be6da02e17432f2ff80a261f
22 1d0cb7cd3f54c15bd41491e6fa449f12
23 15f95be2574a94677fa7e76ead23e025
24 924abdbb21c79cc7fd11d4d5b87ac339
25 7493978704b5223a4f5dc6d8beb51d90
26 f4ac4696b05577ba4a05334cd131cf94
27 cfd45d4915f342978fb6982a825f6cc2
28 2bd8981c6245c32ea1dc27ef394df94e
29 0e47838fe4be239939424fcef1289002
30 18e291b42da10b04434bdad010a5b66c
31 cbd1bf2554378ef7edd9087d4a8562d4
32 ddb1e7282bb637f7fe3aa3aed149b482
33 1963f2400c8870d67f99c391bc5040c2
34 d2b44d3c13a7a2c83de840892a370f47
35 7ff20517acf5ea901757262eae1edf5e
36 685fbfc47faad4243944d7331a2dee36
37 9d58b82b5f9d369a16c4f2bcc424b412
38 be15aa080dda65c9489892c34a330c30
39 4b52ddf7b599c9a00d094008a464c18c
40 441b03789e58aec883138d9c657a005e
41 e76cc7b1cadcb4f31e0232a0a8458620
42 3581a040fbf210f6942fe591621f0e41
43 acc552d3cba6a900054093c3c6876fc5
44 fd7fa7a0a4a58485a1aa30e804f024af
45 56afedd4f2e8101fbb4402051e9857e8
46 019e4178eccbdd84150f196668e40888
47 132c8e314dd7c9064e1cb4980856531c
48 dcc08184bf2f7cfb640af0c603ff7506
49 691b29183f64480003f8a1a74cd3ac13
50 105da23f1b4a63d755414beca9df20d5
51 30b2e973a0186721f9a7720be6be09e5
52 b307d14c06fd6f27173d683a5596b542
53 2125a788f107a4ba6a364984f7145156
54 aa0793982ae401cd2c1bea949d726ae1
55 e1797ba6b7ecf5f84cda2703f79c878c
56 78fc3f94127c0d5c6b3fa82ac3a8c167
57 b8c6d1e26cfd94aade5e32cf89e05da6
58 1e3481d6ff3b22f6ad739b93afe4c341
59 59e9d5be4a87ee707578a3a8e5d624e9
60 7a00e357826e950ffbe688c3f9a99a97
61 e5a8c0d404f6fb59d9e3dd88b13ba5a7
62 19d22c20fbf76b5b9ac746679ade26e7
63 c96f912f54b9b862532ce959398c76ca
64 86c167171b777a150356f1f083203d24
65 e1bca8d6cc1904a919cce1090e62f34d
66 f5dd3f6267376875239222dbefac87b1
67 d6a2aa26124dd42a9df9fa21b7eb8bce
68 ecd36ba9a56f89d4bf6fa83c2b800985
69 87b64475fd3f4db6dd68a45fb9a825dc
70 f5f7aa5569558b3e6556c72c9dfc429b
71 9e1cc67e0c13d00c771b6bed2fd566cf
72 3a6e7c6ce76a04fa2503d094121d84d5
73 5fd8ba2dd8d36814d06a1d278002e437
74 10db883700dde7289eb640df3a6d6676
75 3ebd59ed119d6dfda3eedc5bf05fd425
76 5008d3ce585f3b2dda2b313c57ebcb0a
77 ac7cee33e39827de02acedb7584acae5
78 3f440facefbd95f905942c75bfe60bbb
79 89639b3145c988c775d9d997c9d356ff
80 a6f8b29a83b2305188c241f2724ea998
81 d79f9bd47505cee69627e8044f7745c2
82 5f8d8b55bc9d5b44f817ada0b9bab2f7
83 411a99ef2e2af45640f0224274979fb5
84 60706cd1eb7dffae3d7a34133c5cb67e
85 272a5d09070b5a0569fb18e144100f8b
86 0f969fd16dd186e9eb099213a82fb119
87 6c48f63ec50e26ba42d8ca074daee9a6
88 83512539cc5a2561426564590d307a58
89 c57cf49bbeaa12aaba2106278681c9cd
90 d248507181561f0aba09182c4326fb11
91 a1e153dd8bb1334bccd73890fafc5ba9
92 d7a0ba2d1241a07abf3691074cecff76
93 466034794d577c41a9716bc9eb903ee2
94 b5e2158feb4290b04a7bdfdec0a17976
95 8a16a27008d033dd6e9074b042021e60
96 ccaa12e89f6de3058b3a2baddc4eff00
97 7fdbf02f369546d0fde2b7db85752e33
98 63b286bb777298397f3acf03fc3e9deb
99 b271f751a4952f86b002b9730bd02c8b
100 cbd5cef546acc0bfa33117d7c1ae58a8
show status like '%sort%';
Variable_name Value
Sort_merge_passes 4
Sort_priority_queue_sorts 0
Sort_range 0
Sort_rows 10000
Sort_scan 1
set sort_buffer_size=default;
#
# CASE #1 Packed sort keys with addon fields
#
ALTER TABLE t3 ADD INDEX idx(names, address);
set sort_buffer_size= 2097152;
ANALYZE FORMAT=JSON SELECT id, names, address FROM t3 ORDER BY names, address;
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"read_sorted_file": {
"r_rows": 10000,
"filesort": {
"sort_key": "t3.`names`, t3.address",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"r_used_priority_queue": false,
"r_output_rows": 10000,
"r_buffer_size": "REPLACED",
"r_sort_mode": "packed_sort_key,packed_addon_fields",
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 10000,
"r_rows": 10000,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"filtered": 100,
"r_filtered": 100
}
}
}
}
}
flush status;
SELECT id, names, address INTO OUTFILE '$file1' FROM t3 ORDER BY names, address;
# Sort_merge_passes should be 0
show status like '%sort%';
Variable_name Value
Sort_merge_passes 0
Sort_priority_queue_sorts 0
Sort_range 0
Sort_rows 10000
Sort_scan 1
SELECT id, names, address INTO OUTFILE '$file2' FROM t3 FORCE INDEX(idx) ORDER BY names, address;
#
# CASE #2 Packed sort keys and ROW_ID
#
set @save_max_length_for_sort_data=@@max_length_for_sort_data;
set max_length_for_sort_data= 300;
set sort_buffer_size= 1097152;
ANALYZE FORMAT=JSON SELECT id, names, address FROM t3 ORDER BY names, address;
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"read_sorted_file": {
"r_rows": 10000,
"filesort": {
"sort_key": "t3.`names`, t3.address",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"r_used_priority_queue": false,
"r_output_rows": 10000,
"r_buffer_size": "REPLACED",
"r_sort_mode": "packed_sort_key,rowid",
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 10000,
"r_rows": 10000,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"filtered": 100,
"r_filtered": 100
}
}
}
}
}
flush status;
SELECT id, names, address INTO OUTFILE '$file1' FROM t3 ORDER BY names, address;
# Sort_merge_passes should be 0
show status like '%sort%';
Variable_name Value
Sort_merge_passes 0
Sort_priority_queue_sorts 0
Sort_range 0
Sort_rows 10000
Sort_scan 1
set @@max_length_for_sort_data=@save_max_length_for_sort_data;
set @@sort_buffer_size=default;
set @@RAND_SEED1= @save_rand_seed1;
set @@RAND_SEED2= @save_rand_seed2;
drop function generate_normal_distribution_sample;
drop function generate_random_string;
drop function clipped_normal_distribution;
drop table t1, t2, t3;
|
