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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "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", "r_loops": 1, "rows": 10000, "r_rows": 10000, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "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", "r_loops": 1, "rows": 10000, "r_rows": 10000, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "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", "r_loops": 1, "rows": 10000, "r_rows": 10000, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "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", "r_loops": 1, "rows": 10000, "r_rows": 10000, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": 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;