diff options
Diffstat (limited to 'mysql-test/main/order_by_pack_big.result')
| -rw-r--r-- | mysql-test/main/order_by_pack_big.result | 531 |
1 files changed, 531 insertions, 0 deletions
diff --git a/mysql-test/main/order_by_pack_big.result b/mysql-test/main/order_by_pack_big.result new file mode 100644 index 00000000..eda697fd --- /dev/null +++ b/mysql-test/main/order_by_pack_big.result @@ -0,0 +1,531 @@ +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; |