mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
545 lines
16 KiB
Text
545 lines
16 KiB
Text
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)
|
|
) CHARSET=latin1;
|
|
#
|
|
# 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)) as md5
|
|
FROM t3
|
|
GROUP BY x;
|
|
ANALYZE
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": "REPLACED"
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": "REPLACED",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": "REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"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",
|
|
"loops": 1,
|
|
"r_loops": 1,
|
|
"rows": 10000,
|
|
"r_rows": 10000,
|
|
"cost": "REPLACED",
|
|
"r_table_time_ms": "REPLACED",
|
|
"r_other_time_ms": "REPLACED",
|
|
"r_engine_stats": REPLACED,
|
|
"filtered": 100,
|
|
"r_total_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)) as md5
|
|
FROM t3
|
|
GROUP BY x;
|
|
x md5
|
|
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)) as md5
|
|
FROM t3
|
|
GROUP BY x;
|
|
ANALYZE
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": "REPLACED"
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": "REPLACED",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": "REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"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",
|
|
"loops": 1,
|
|
"r_loops": 1,
|
|
"rows": 10000,
|
|
"r_rows": 10000,
|
|
"cost": "REPLACED",
|
|
"r_table_time_ms": "REPLACED",
|
|
"r_other_time_ms": "REPLACED",
|
|
"r_engine_stats": REPLACED,
|
|
"filtered": 100,
|
|
"r_total_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)) as md5
|
|
FROM t3
|
|
GROUP BY x;
|
|
x md5
|
|
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_optimization": {
|
|
"r_total_time_ms": "REPLACED"
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": "REPLACED",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": "REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"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",
|
|
"loops": 1,
|
|
"r_loops": 1,
|
|
"rows": 10000,
|
|
"r_rows": 10000,
|
|
"cost": "REPLACED",
|
|
"r_table_time_ms": "REPLACED",
|
|
"r_other_time_ms": "REPLACED",
|
|
"r_engine_stats": REPLACED,
|
|
"filtered": 100,
|
|
"r_total_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_optimization": {
|
|
"r_total_time_ms": "REPLACED"
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": "REPLACED",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": "REPLACED",
|
|
"nested_loop": [
|
|
{
|
|
"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",
|
|
"loops": 1,
|
|
"r_loops": 1,
|
|
"rows": 10000,
|
|
"r_rows": 10000,
|
|
"cost": "REPLACED",
|
|
"r_table_time_ms": "REPLACED",
|
|
"r_other_time_ms": "REPLACED",
|
|
"r_engine_stats": REPLACED,
|
|
"filtered": 100,
|
|
"r_total_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;
|