-------------- CREATE OR REPLACE TABLE replay_results( event_id BIGINT UNSIGNED NOT NULL PRIMARY KEY, canonical INT UNSIGNED NOT NULL, duration DOUBLE NOT NULL, start_time DOUBLE NOT NULL, result_rows INT UNSIGNED NOT NULL, rows_read INT UNSIGNED NOT NULL, error SMALLINT UNSIGNED DEFAULT(0) ) ENGINE=MyISAM -------------- Query OK, 0 rows affected (0.097 sec) -------------- CREATE OR REPLACE TABLE canonicals( canonical INT UNSIGNED NOT NULL PRIMARY KEY, canonical_sql MEDIUMTEXT NOT NULL ) ENGINE=MyISAM -------------- Query OK, 0 rows affected (0.001 sec) -------------- LOAD DATA LOCAL INFILE 'replay.csv' INTO TABLE replay_results FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (event_id, canonical, duration, start_time, result_rows, rows_read, @err) SET error = 0 -------------- Query OK, 14149445 rows affected (23.624 sec) Records: 14149445 Deleted: 0 Skipped: 0 Warnings: 0 -------------- LOAD DATA LOCAL INFILE 'canonicals.csv' INTO TABLE canonicals FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES -------------- Query OK, 46 rows affected (0.000 sec) Records: 46 Deleted: 0 Skipped: 0 Warnings: 0 -------------- ANALYZE FORMAT=JSON WITH can_summary AS ( SELECT canonical, MIN(duration) dur_min, MAX(duration) dur_max, SUM(duration) dur_sum, AVG(duration) dur_avg, STDDEV(duration) dur_stddev, MIN(rows_read) rr_min, MAX(rows_read) rr_max, SUM(rows_read) rr_sum, AVG(rows_read) rr_avg, STDDEV(rows_read) rr_stddev FROM replay_results GROUP BY canonical ), can_summary_sql AS( SELECT canonical_sql, can_summary.* FROM can_summary JOIN canonicals ON (canonicals.canonical = can_summary.canonical) ), hist_limits AS ( SELECT canonical, dur_avg + dur_stddev * 3 val_max, dur_min val_min, (dur_avg + dur_stddev * 3 - dur_min) / 100 bin_width FROM can_summary GROUP BY canonical ), hist_data AS ( SELECT a.canonical, FLOOR((duration - b.val_min) / b.bin_width) bin_number, b.val_min + FLOOR((duration - b.val_min) / b.bin_width) * b.bin_width bin_value, COUNT(duration) cnt FROM replay_results a JOIN hist_limits b ON (a.canonical = b.canonical) WHERE a.duration < b.val_max GROUP BY 1, 2 ), hist_empty_range AS ( SELECT canonical, seq bin_number, val_min + seq * bin_width bin_value FROM hist_limits JOIN seq_0_to_100 GROUP BY 1, 2 ), json_histogram AS( SELECT a.canonical, JSON_OBJECT( 'id', a.canonical, 'sql', cs.canonical_sql, 'dur_min', cs.dur_min, 'dur_max', cs.dur_max, 'dur_sum', cs.dur_sum, 'dur_avg', cs.dur_avg, 'dur_stddev', cs.dur_stddev, 'rr_min', cs.rr_min, 'rr_max', cs.rr_max, 'rr_sum', cs.rr_sum, 'rr_avg', cs.rr_avg, 'rr_stddev', cs.rr_stddev, 'bins', JSON_ARRAYAGG(a.bin_value ORDER BY a.bin_number), 'counts', JSON_ARRAYAGG(COALESCE(b.cnt, 0) ORDER BY a.bin_number) ) js FROM hist_empty_range a LEFT JOIN hist_data b ON (a.canonical = b.canonical AND a.bin_number = b.bin_number) JOIN can_summary_sql cs ON (a.canonical = cs.canonical) GROUP BY 1 ) SELECT JSON_ARRAYAGG(js) FROM json_histogram -------------- *************************** 1. row *************************** ANALYZE: { "query_optimization": { "r_total_time_ms": 0.453104876 }, "query_block": { "select_id": 1, "cost": 1312452.45, "r_loops": 1, "r_total_time_ms": 17986.03723, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 6573832147, "r_rows": 45, "cost": 1312452.45, "r_table_time_ms": 0.070336326, "r_other_time_ms": 0.143243446, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 7, "cost": 5620320.53, "r_loops": 1, "r_total_time_ms": 17985.86174, "nested_loop": [ { "read_sorted_file": { "r_rows": 46, "filesort": { "sort_key": "a.canonical", "r_loops": 1, "r_total_time_ms": 0.017808235, "r_used_priority_queue": false, "r_output_rows": 46, "r_buffer_size": "1008", "r_sort_mode": "sort_key,rowid", "table": { "table_name": "canonicals", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1, "r_loops": 1, "rows": 46, "r_rows": 46, "cost": 0.01747063, "r_table_time_ms": 0.051408395, "r_other_time_ms": 0.009647542, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key1"], "key": "key1", "key_length": "4", "used_key_parts": ["canonical"], "ref": ["test.canonicals.canonical"], "loops": 46, "r_loops": 46, "rows": 141494, "r_rows": 0.97826087, "cost": 727.1887472, "r_table_time_ms": 0.013821018, "r_other_time_ms": 0.09624698, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 2, "cost": 12033.00477, "r_loops": 1, "r_total_time_ms": 4463.812458, "filesort": { "sort_key": "replay_results.canonical", "r_loops": 1, "r_total_time_ms": 0.027719256, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "replay_results", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 14149445, "cost": 2245.44139, "r_table_time_ms": 282.627063, "r_other_time_ms": 4181.110511, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } } ] } } } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["canonical"], "ref": ["test.canonicals.canonical"], "loops": 6508744.7, "r_loops": 45, "rows": 101, "r_rows": 101, "cost": 74493.19491, "r_table_time_ms": 0.310446303, "r_other_time_ms": 0.792196365, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 6, "cost": 133346.5262, "r_loops": 1, "r_total_time_ms": 4500.948819, "filesort": { "sort_key": "hist_limits.canonical, seq_0_to_100.seq", "r_loops": 1, "r_total_time_ms": 0.299210738, "r_used_priority_queue": false, "r_output_rows": 4545, "r_buffer_size": "124Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "seq_0_to_100", "access_type": "index", "key": "PRIMARY", "key_length": "8", "used_key_parts": ["seq"], "loops": 1, "r_loops": 1, "rows": 101, "r_rows": 101, "cost": 0.009498548, "r_table_time_ms": 0.003209651, "r_other_time_ms": 0.007752068, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true } }, { "block-nl-join": { "table": { "table_name": "", "access_type": "ALL", "loops": 101, "r_loops": 1, "rows": 14149445, "r_rows": 45, "cost": 133346.5167, "r_table_time_ms": 0.002860729, "r_other_time_ms": 0.009563172, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "917", "join_type": "BNL", "r_loops": 101, "r_filtered": 100, "r_unpack_time_ms": 0.073453383, "r_other_time_ms": 0.621593309, "r_effective_rows": 45, "materialized": { "query_block": { "select_id": 9, "cost": 10419.83567, "r_loops": 1, "r_total_time_ms": 4499.372856, "filesort": { "sort_key": "can_summary.canonical", "r_loops": 1, "r_total_time_ms": 0.003706578, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 45, "cost": 632.27229, "r_table_time_ms": 0.00501003, "r_other_time_ms": 0.043390346, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 10, "cost": 12033.00477, "r_loops": 1, "r_total_time_ms": 4499.333603, "filesort": { "sort_key": "replay_results.canonical", "r_loops": 1, "r_total_time_ms": 0.035884954, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "replay_results", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 14149445, "cost": 2245.44139, "r_table_time_ms": 287.3291374, "r_other_time_ms": 4211.92555, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } } ] } } } } } } ] } } } } } } ] } } } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "14", "used_key_parts": ["canonical", "bin_number"], "ref": ["test.canonicals.canonical", "a.bin_number"], "loops": 657383214.7, "r_loops": 4545, "rows": 10, "r_rows": 0.831023102, "cost": 840273.7989, "r_table_time_ms": 0.787447669, "r_other_time_ms": 8.284072529, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(a.bin_number = b.bin_number)", "materialized": { "query_block": { "select_id": 5, "cost": 20331.40508, "r_loops": 1, "r_total_time_ms": 9011.984185, "filesort": { "sort_key": "a.canonical, floor((a.duration - b.val_min) / b.bin_width)", "r_loops": 1, "r_total_time_ms": 0.534110484, "r_used_priority_queue": false, "r_output_rows": 3777, "r_buffer_size": "110Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 14149445, "cost": 2245.44139, "r_table_time_ms": 278.6570518, "r_other_time_ms": 663.8176591, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["canonical"], "ref": ["test.a.canonical"], "loops": 14149445, "r_loops": 14149445, "rows": 10, "r_rows": 1, "cost": 18085.96369, "r_table_time_ms": 825.1313506, "r_other_time_ms": 2748.666245, "filtered": 100, "r_filtered": 98.74845268, "attached_condition": "a.duration < b.val_max", "materialized": { "query_block": { "select_id": 4, "cost": 10419.83567, "r_loops": 1, "r_total_time_ms": 4494.423365, "filesort": { "sort_key": "can_summary.canonical", "r_loops": 1, "r_total_time_ms": 0.002963332, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 45, "cost": 632.27229, "r_table_time_ms": 0.022692066, "r_other_time_ms": 0.049350972, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 8, "cost": 12033.00477, "r_loops": 1, "r_total_time_ms": 4494.378474, "filesort": { "sort_key": "replay_results.canonical", "r_loops": 1, "r_total_time_ms": 0.013963661, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "replay_results", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 14149445, "cost": 2245.44139, "r_table_time_ms": 282.4808863, "r_other_time_ms": 4211.818635, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } } ] } } } } } } ] } } } } } } ] } } } } } } ] } } } } ] } } 1 row in set (17.949 sec) -------------- CREATE OR REPLACE TEMPORARY TABLE can_summary AS WITH can_summary AS ( SELECT canonical, MIN(duration) dur_min, MAX(duration) dur_max, SUM(duration) dur_sum, AVG(duration) dur_avg, STDDEV(duration) dur_stddev, MIN(rows_read) rr_min, MAX(rows_read) rr_max, SUM(rows_read) rr_sum, AVG(rows_read) rr_avg, STDDEV(rows_read) rr_stddev FROM replay_results GROUP BY canonical ), can_summary_sql AS( SELECT canonical_sql, can_summary.* FROM can_summary JOIN canonicals ON (canonicals.canonical = can_summary.canonical) ) SELECT * FROM can_summary_sql -------------- Query OK, 45 rows affected (4.449 sec) Records: 45 Duplicates: 0 Warnings: 0 -------------- ANALYZE FORMAT=JSON WITH hist_limits AS ( SELECT canonical, dur_avg + dur_stddev * 3 val_max, dur_min val_min, (dur_avg + dur_stddev * 3 - dur_min) / 100 bin_width FROM can_summary GROUP BY canonical ), hist_data AS ( SELECT a.canonical, FLOOR((duration - b.val_min) / b.bin_width) bin_number, b.val_min + FLOOR((duration - b.val_min) / b.bin_width) * b.bin_width bin_value, COUNT(duration) cnt FROM replay_results a JOIN hist_limits b ON (a.canonical = b.canonical) WHERE a.duration < b.val_max GROUP BY 1, 2 ), hist_empty_range AS ( SELECT canonical, seq bin_number, val_min + seq * bin_width bin_value FROM hist_limits JOIN seq_0_to_100 GROUP BY 1, 2 ), json_histogram AS( SELECT a.canonical, JSON_OBJECT( 'id', a.canonical, 'sql', cs.canonical_sql, 'dur_min', cs.dur_min, 'dur_max', cs.dur_max, 'dur_sum', cs.dur_sum, 'dur_avg', cs.dur_avg, 'dur_stddev', cs.dur_stddev, 'rr_min', cs.rr_min, 'rr_max', cs.rr_max, 'rr_sum', cs.rr_sum, 'rr_avg', cs.rr_avg, 'rr_stddev', cs.rr_stddev, 'bins', JSON_ARRAYAGG(a.bin_value ORDER BY a.bin_number), 'counts', JSON_ARRAYAGG(COALESCE(b.cnt, 0) ORDER BY a.bin_number) ) js FROM hist_empty_range a LEFT JOIN hist_data b ON (a.canonical = b.canonical AND a.bin_number = b.bin_number) JOIN can_summary cs ON (a.canonical = cs.canonical) GROUP BY 1 ) SELECT JSON_ARRAYAGG(js) FROM json_histogram -------------- *************************** 1. row *************************** ANALYZE: { "query_optimization": { "r_total_time_ms": 0.399993343 }, "query_block": { "select_id": 1, "cost": 5720.449276, "r_loops": 1, "r_total_time_ms": 4355.33817, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 28652625, "r_rows": 45, "cost": 5720.449276, "r_table_time_ms": 0.075647014, "r_other_time_ms": 0.140111731, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 5, "cost": 23100.40858, "r_loops": 1, "r_total_time_ms": 4355.16236, "nested_loop": [ { "read_sorted_file": { "r_rows": 45, "filesort": { "sort_key": "a.canonical", "r_loops": 1, "r_total_time_ms": 0.02446885, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "5Kb", "r_sort_mode": "sort_key,rowid", "table": { "table_name": "cs", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.067548169, "r_other_time_ms": 0.009502397, "r_engine_stats": { "pages_accessed": 46 }, "filtered": 100, "r_filtered": 100 } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["canonical"], "ref": ["test.cs.canonical"], "loops": 45, "r_loops": 45, "rows": 45, "r_rows": 101, "cost": 0.23413275, "r_table_time_ms": 0.310136706, "r_other_time_ms": 0.887726912, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 4, "cost": 0.448553265, "r_loops": 1, "r_total_time_ms": 1.71740519, "filesort": { "sort_key": "hist_limits.canonical, seq_0_to_100.seq", "r_loops": 1, "r_total_time_ms": 0.286066113, "r_used_priority_queue": false, "r_output_rows": 4545, "r_buffer_size": "124Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.014239777, "r_table_time_ms": 0.001774996, "r_other_time_ms": 0.011564467, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 6, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.039042767, "filesort": { "sort_key": "can_summary.canonical", "r_loops": 1, "r_total_time_ms": 0.00307094, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.010167707, "r_other_time_ms": 0.014081279, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 100 } } ] } } } } } }, { "block-nl-join": { "table": { "table_name": "seq_0_to_100", "access_type": "index", "key": "PRIMARY", "key_length": "8", "used_key_parts": ["seq"], "loops": 45, "r_loops": 1, "rows": 101, "r_rows": 101, "cost": 0.434313488, "r_table_time_ms": 0.002262629, "r_other_time_ms": 0.003127783, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true }, "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", "r_loops": 45, "r_filtered": 100, "r_unpack_time_ms": 0.152440179, "r_other_time_ms": 0.655142704, "r_effective_rows": 101 } } ] } } } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "14", "used_key_parts": ["canonical", "bin_number"], "ref": ["test.cs.canonical", "a.bin_number"], "loops": 2045.25, "r_loops": 4545, "rows": 14009, "r_rows": 0.831023102, "cost": 3201.457906, "r_table_time_ms": 0.732463541, "r_other_time_ms": 8.475460737, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(a.bin_number = b.bin_number)", "materialized": { "query_block": { "select_id": 3, "cost": 11072.79904, "r_loops": 1, "r_total_time_ms": 4344.087993, "filesort": { "sort_key": "a.canonical, floor((a.duration - b.val_min) / b.bin_width)", "r_loops": 1, "r_total_time_ms": 0.498300248, "r_used_priority_queue": false, "r_output_rows": 3777, "r_buffer_size": "110Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 14149445, "cost": 2245.44139, "r_table_time_ms": 273.4844647, "r_other_time_ms": 626.1172299, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["canonical"], "ref": ["test.a.canonical"], "loops": 14149445, "r_loops": 14149445, "rows": 4, "r_rows": 1, "cost": 8827.357654, "r_table_time_ms": 813.0683865, "r_other_time_ms": 2630.214214, "filtered": 100, "r_filtered": 98.74845268, "attached_condition": "a.duration < b.val_max", "materialized": { "query_block": { "select_id": 2, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.034183603, "filesort": { "sort_key": "can_summary.canonical", "r_loops": 1, "r_total_time_ms": 0.003957187, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.011295983, "r_other_time_ms": 0.009275741, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 100 } } ] } } } } } } ] } } } } } } ] } } } } ] } } 1 row in set (4.347 sec) -------------- SELECT canonical, COUNT(*) FROM replay_results GROUP BY canonical ORDER by 2 DESC LIMIT 1 -------------- +-----------+----------+ | canonical | COUNT(*) | +-----------+----------+ | 11 | 2169945 | +-----------+----------+ 1 row in set (1.287 sec) -------------- SELECT canonical, COUNT(*) FROM replay_results GROUP BY canonical ORDER by 2 ASC LIMIT 1 -------------- +-----------+----------+ | canonical | COUNT(*) | +-----------+----------+ | 45 | 1 | +-----------+----------+ 1 row in set (1.274 sec) -------------- ANALYZE FORMAT=JSON WITH hist_limits AS ( SELECT canonical, dur_avg + dur_stddev * 3 val_max, dur_min val_min, (dur_avg + dur_stddev * 3 - dur_min) / 100 bin_width FROM can_summary GROUP BY canonical ), hist_data AS ( SELECT a.canonical, FLOOR((duration - b.val_min) / b.bin_width) bin_number, b.val_min + FLOOR((duration - b.val_min) / b.bin_width) * b.bin_width bin_value, COUNT(duration) cnt FROM replay_results a JOIN hist_limits b ON (a.canonical = b.canonical) WHERE a.duration < b.val_max GROUP BY 1, 2 ), hist_empty_range AS ( SELECT canonical, seq bin_number, val_min + seq * bin_width bin_value FROM hist_limits JOIN seq_0_to_100 GROUP BY 1, 2 ), json_histogram AS( SELECT a.canonical, JSON_OBJECT( 'id', a.canonical, 'sql', cs.canonical_sql, 'dur_min', cs.dur_min, 'dur_max', cs.dur_max, 'dur_sum', cs.dur_sum, 'dur_avg', cs.dur_avg, 'dur_stddev', cs.dur_stddev, 'rr_min', cs.rr_min, 'rr_max', cs.rr_max, 'rr_sum', cs.rr_sum, 'rr_avg', cs.rr_avg, 'rr_stddev', cs.rr_stddev, 'bins', JSON_ARRAYAGG(a.bin_value ORDER BY a.bin_number), 'counts', JSON_ARRAYAGG(COALESCE(b.cnt, 0) ORDER BY a.bin_number) ) js FROM hist_empty_range a LEFT JOIN hist_data b ON (a.canonical = b.canonical AND a.bin_number = b.bin_number) JOIN can_summary cs ON (a.canonical = cs.canonical) GROUP BY 1 ) SELECT JSON_ARRAYAGG(js) FROM json_histogram WHERE canonical = 45 -------------- *************************** 1. row *************************** ANALYZE: { "query_optimization": { "r_total_time_ms": 0.440048197 }, "query_block": { "select_id": 1, "cost": 572043.9556, "r_loops": 1, "r_total_time_ms": 4521.894696, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 2865262590, "r_rows": 1, "cost": 572043.9556, "r_table_time_ms": 0.037959179, "r_other_time_ms": 0.01933727, "filtered": 100, "r_filtered": 100, "attached_condition": "json_histogram.canonical = 45", "materialized": { "query_block": { "select_id": 5, "cost": 2361520.686, "r_loops": 1, "r_total_time_ms": 4521.805235, "nested_loop": [ { "table": { "table_name": "cs", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.023500735, "r_other_time_ms": 0.004577452, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "cs.canonical = 45" } }, { "block-nl-join": { "table": { "table_name": "", "access_type": "ALL", "loops": 45, "r_loops": 1, "rows": 4545, "r_rows": 101, "cost": 19.20924913, "r_table_time_ms": 0.004155958, "r_other_time_ms": 0.098243985, "filtered": 100, "r_filtered": 100, "attached_condition": "a.canonical = 45" }, "buffer_type": "flat", "buffer_size": "22Kb", "join_type": "BNL", "r_loops": 1, "r_filtered": 100, "r_unpack_time_ms": 0.010188085, "r_other_time_ms": 0.006596265, "r_effective_rows": 101, "materialized": { "query_block": { "select_id": 4, "cost": 0.448553265, "r_loops": 1, "r_total_time_ms": 0.070890453, "filesort": { "sort_key": "seq_0_to_100.seq", "r_loops": 1, "r_total_time_ms": 0.006944829, "r_used_priority_queue": false, "r_output_rows": 101, "r_buffer_size": "2Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 1, "cost": 0.014239777, "r_table_time_ms": 0.001480057, "r_other_time_ms": 0.001909774, "filtered": 100, "r_filtered": 100, "attached_condition": "hist_limits.canonical = 45", "materialized": { "query_block": { "select_id": 6, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.02130782, "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.00865762, "r_other_time_ms": 0.009357966, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "can_summary.canonical = 45" } } ] } } } }, { "block-nl-join": { "table": { "table_name": "seq_0_to_100", "access_type": "index", "key": "PRIMARY", "key_length": "8", "used_key_parts": ["seq"], "loops": 45, "r_loops": 1, "rows": 101, "r_rows": 101, "cost": 0.434313488, "r_table_time_ms": 0.002127135, "r_other_time_ms": 0.003181051, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true }, "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", "r_loops": 1, "r_filtered": 100, "r_unpack_time_ms": 0.003695138, "r_other_time_ms": 0.016205555, "r_effective_rows": 101 } } ] } } } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "9", "used_key_parts": ["bin_number"], "ref": ["a.bin_number"], "loops": 204525, "r_loops": 101, "rows": 14009, "r_rows": 37.3960396, "cost": 320145.7906, "r_table_time_ms": 0.275385391, "r_other_time_ms": 0.593255932, "filtered": 100, "r_filtered": 0, "attached_condition": "trigcond(b.canonical = 45 and a.bin_number = b.bin_number)", "materialized": { "query_block": { "select_id": 3, "cost": 11072.79904, "r_loops": 1, "r_total_time_ms": 4521.272127, "filesort": { "sort_key": "a.canonical, floor((a.duration - b.val_min) / b.bin_width)", "r_loops": 1, "r_total_time_ms": 0.536433531, "r_used_priority_queue": false, "r_output_rows": 3777, "r_buffer_size": "110Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 14149445, "cost": 2245.44139, "r_table_time_ms": 282.5295036, "r_other_time_ms": 647.7282021, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["canonical"], "ref": ["test.a.canonical"], "loops": 14149445, "r_loops": 14149445, "rows": 4, "r_rows": 1, "cost": 8827.357654, "r_table_time_ms": 843.1903502, "r_other_time_ms": 2746.668076, "filtered": 100, "r_filtered": 98.74845268, "attached_condition": "a.duration < b.val_max", "materialized": { "query_block": { "select_id": 2, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.033518292, "filesort": { "sort_key": "can_summary.canonical", "r_loops": 1, "r_total_time_ms": 0.002913639, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.009486667, "r_other_time_ms": 0.009347956, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 100 } } ] } } } } } } ] } } } } } } ] } } } } ] } } 1 row in set (4.513 sec) -------------- ANALYZE FORMAT=JSON WITH hist_limits AS ( SELECT canonical, dur_avg + dur_stddev * 3 val_max, dur_min val_min, (dur_avg + dur_stddev * 3 - dur_min) / 100 bin_width FROM can_summary GROUP BY canonical ), hist_data AS ( SELECT a.canonical, FLOOR((duration - b.val_min) / b.bin_width) bin_number, b.val_min + FLOOR((duration - b.val_min) / b.bin_width) * b.bin_width bin_value, COUNT(duration) cnt FROM replay_results a JOIN hist_limits b ON (a.canonical = b.canonical) WHERE a.duration < b.val_max AND a.canonical = 45 GROUP BY 1, 2 ), hist_empty_range AS ( SELECT canonical, seq bin_number, val_min + seq * bin_width bin_value FROM hist_limits JOIN seq_0_to_100 GROUP BY 1, 2 ), json_histogram AS( SELECT a.canonical, JSON_OBJECT( 'id', a.canonical, 'sql', cs.canonical_sql, 'dur_min', cs.dur_min, 'dur_max', cs.dur_max, 'dur_sum', cs.dur_sum, 'dur_avg', cs.dur_avg, 'dur_stddev', cs.dur_stddev, 'rr_min', cs.rr_min, 'rr_max', cs.rr_max, 'rr_sum', cs.rr_sum, 'rr_avg', cs.rr_avg, 'rr_stddev', cs.rr_stddev, 'bins', JSON_ARRAYAGG(a.bin_value ORDER BY a.bin_number), 'counts', JSON_ARRAYAGG(COALESCE(b.cnt, 0) ORDER BY a.bin_number) ) js FROM hist_empty_range a LEFT JOIN hist_data b ON (a.canonical = b.canonical AND a.bin_number = b.bin_number) JOIN can_summary cs ON (a.canonical = cs.canonical) GROUP BY 1 ) SELECT JSON_ARRAYAGG(js) FROM json_histogram WHERE canonical = 45 -------------- *************************** 1. row *************************** ANALYZE: { "query_optimization": { "r_total_time_ms": 0.334090754 }, "query_block": { "select_id": 1, "cost": 5720439.511, "r_loops": 1, "r_total_time_ms": 450.357117, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 28652626125, "r_rows": 1, "cost": 5720439.511, "r_table_time_ms": 0.026796544, "r_other_time_ms": 0.015774408, "filtered": 100, "r_filtered": 100, "attached_condition": "json_histogram.canonical = 45", "materialized": { "query_block": { "select_id": 5, "cost": 23872215.74, "r_loops": 1, "r_total_time_ms": 450.2816198, "nested_loop": [ { "table": { "table_name": "cs", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.024616141, "r_other_time_ms": 0.003958259, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "cs.canonical = 45" } }, { "block-nl-join": { "table": { "table_name": "", "access_type": "ALL", "loops": 45, "r_loops": 1, "rows": 4545, "r_rows": 101, "cost": 19.20924913, "r_table_time_ms": 0.003811684, "r_other_time_ms": 0.095018247, "filtered": 100, "r_filtered": 100, "attached_condition": "a.canonical = 45" }, "buffer_type": "flat", "buffer_size": "22Kb", "join_type": "BNL", "r_loops": 1, "r_filtered": 100, "r_unpack_time_ms": 0.008916094, "r_other_time_ms": 0.006583037, "r_effective_rows": 101, "materialized": { "query_block": { "select_id": 4, "cost": 0.448553265, "r_loops": 1, "r_total_time_ms": 0.071635129, "filesort": { "sort_key": "seq_0_to_100.seq", "r_loops": 1, "r_total_time_ms": 0.007737768, "r_used_priority_queue": false, "r_output_rows": 101, "r_buffer_size": "2Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 1, "cost": 0.014239777, "r_table_time_ms": 6.345656e-4, "r_other_time_ms": 0.00209782, "filtered": 100, "r_filtered": 100, "attached_condition": "hist_limits.canonical = 45", "materialized": { "query_block": { "select_id": 6, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.018351995, "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.009068747, "r_other_time_ms": 0.006927669, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "can_summary.canonical = 45" } } ] } } } }, { "block-nl-join": { "table": { "table_name": "seq_0_to_100", "access_type": "index", "key": "PRIMARY", "key_length": "8", "used_key_parts": ["seq"], "loops": 45, "r_loops": 1, "rows": 101, "r_rows": 101, "cost": 0.434313488, "r_table_time_ms": 0.002371667, "r_other_time_ms": 0.003927514, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true }, "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", "r_loops": 1, "r_filtered": 100, "r_unpack_time_ms": 0.004047992, "r_other_time_ms": 0.017309878, "r_effective_rows": 101 } } ] } } } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "9", "used_key_parts": ["bin_number"], "ref": ["a.bin_number"], "loops": 204525, "r_loops": 101, "rows": 140093, "r_rows": 0, "cost": 3201210.529, "r_table_time_ms": 0.002616198, "r_other_time_ms": 0.073970331, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(b.canonical = 45 and a.bin_number = b.bin_number)", "materialized": { "query_block": { "select_id": 3, "cost": 61375.56454, "r_loops": 1, "r_total_time_ms": 449.9673475, "filesort": { "sort_key": "floor((a.duration - b.val_min) / b.bin_width)", "r_loops": 1, "r_total_time_ms": 0.006246628, "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "376", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 1, "cost": 0.014239777, "r_table_time_ms": 2.184336e-4, "r_other_time_ms": 0.002221158, "filtered": 100, "r_filtered": 100, "attached_condition": "b.canonical = 45", "materialized": { "query_block": { "select_id": 2, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.0132483, "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.00920138, "r_other_time_ms": 0.002825336, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "can_summary.canonical = 45" } } ] } } } }, { "block-nl-join": { "table": { "table_name": "a", "access_type": "ALL", "loops": 45, "r_loops": 1, "rows": 14149445, "r_rows": 14149445, "cost": 61375.5503, "r_table_time_ms": 215.2430938, "r_other_time_ms": 234.6898151, "r_engine_stats": {}, "filtered": 100, "r_filtered": 7.067415e-6, "attached_condition": "a.canonical = 45" }, "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", "attached_condition": "a.duration < b.val_max", "r_loops": 1, "r_filtered": 0, "r_unpack_time_ms": 8.351241e-4, "r_other_time_ms": 6.842584e-4, "r_effective_rows": 1 } } ] } } } } } } ] } } } } ] } } 1 row in set (0.450 sec) -------------- CREATE INDEX canonical_idx ON replay_results(canonical) -------------- Query OK, 14149445 rows affected (11.110 sec) Records: 14149445 Duplicates: 0 Warnings: 0 -------------- ANALYZE FORMAT=JSON WITH hist_limits AS ( SELECT canonical, dur_avg + dur_stddev * 3 val_max, dur_min val_min, (dur_avg + dur_stddev * 3 - dur_min) / 100 bin_width FROM can_summary GROUP BY canonical ), hist_data AS ( SELECT a.canonical, FLOOR((duration - b.val_min) / b.bin_width) bin_number, b.val_min + FLOOR((duration - b.val_min) / b.bin_width) * b.bin_width bin_value, COUNT(duration) cnt FROM replay_results a JOIN hist_limits b ON (a.canonical = b.canonical) WHERE a.duration < b.val_max GROUP BY 1, 2 ), hist_empty_range AS ( SELECT canonical, seq bin_number, val_min + seq * bin_width bin_value FROM hist_limits JOIN seq_0_to_100 GROUP BY 1, 2 ), json_histogram AS( SELECT a.canonical, JSON_OBJECT( 'id', a.canonical, 'sql', cs.canonical_sql, 'dur_min', cs.dur_min, 'dur_max', cs.dur_max, 'dur_sum', cs.dur_sum, 'dur_avg', cs.dur_avg, 'dur_stddev', cs.dur_stddev, 'rr_min', cs.rr_min, 'rr_max', cs.rr_max, 'rr_sum', cs.rr_sum, 'rr_avg', cs.rr_avg, 'rr_stddev', cs.rr_stddev, 'bins', JSON_ARRAYAGG(a.bin_value ORDER BY a.bin_number), 'counts', JSON_ARRAYAGG(COALESCE(b.cnt, 0) ORDER BY a.bin_number) ) js FROM hist_empty_range a LEFT JOIN hist_data b ON (a.canonical = b.canonical AND a.bin_number = b.bin_number) JOIN can_summary cs ON (a.canonical = cs.canonical) GROUP BY 1 ) SELECT JSON_ARRAYAGG(js) FROM json_histogram WHERE canonical = 45 -------------- *************************** 1. row *************************** ANALYZE: { "query_optimization": { "r_total_time_ms": 0.375671783 }, "query_block": { "select_id": 1, "cost": 572043.9556, "r_loops": 1, "r_total_time_ms": 4552.746191, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 2865262590, "r_rows": 1, "cost": 572043.9556, "r_table_time_ms": 0.041514891, "r_other_time_ms": 0.019473121, "filtered": 100, "r_filtered": 100, "attached_condition": "json_histogram.canonical = 45", "materialized": { "query_block": { "select_id": 5, "cost": 2361520.686, "r_loops": 1, "r_total_time_ms": 4552.669908, "nested_loop": [ { "table": { "table_name": "cs", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.022007451, "r_other_time_ms": 0.005597763, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "cs.canonical = 45" } }, { "block-nl-join": { "table": { "table_name": "", "access_type": "ALL", "loops": 45, "r_loops": 1, "rows": 4545, "r_rows": 101, "cost": 19.20924913, "r_table_time_ms": 0.003530687, "r_other_time_ms": 0.095832993, "filtered": 100, "r_filtered": 100, "attached_condition": "a.canonical = 45" }, "buffer_type": "flat", "buffer_size": "22Kb", "join_type": "BNL", "r_loops": 1, "r_filtered": 100, "r_unpack_time_ms": 0.010022919, "r_other_time_ms": 0.006631658, "r_effective_rows": 101, "materialized": { "query_block": { "select_id": 4, "cost": 0.448553265, "r_loops": 1, "r_total_time_ms": 0.068937779, "filesort": { "sort_key": "seq_0_to_100.seq", "r_loops": 1, "r_total_time_ms": 0.00755437, "r_used_priority_queue": false, "r_output_rows": 101, "r_buffer_size": "2Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 1, "cost": 0.014239777, "r_table_time_ms": 8.297616e-4, "r_other_time_ms": 0.002588313, "filtered": 100, "r_filtered": 100, "attached_condition": "hist_limits.canonical = 45", "materialized": { "query_block": { "select_id": 6, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.021677119, "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.007890779, "r_other_time_ms": 0.010442984, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "can_summary.canonical = 45" } } ] } } } }, { "block-nl-join": { "table": { "table_name": "seq_0_to_100", "access_type": "index", "key": "PRIMARY", "key_length": "8", "used_key_parts": ["seq"], "loops": 45, "r_loops": 1, "rows": 101, "r_rows": 101, "cost": 0.434313488, "r_table_time_ms": 0.001527248, "r_other_time_ms": 0.004323626, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true }, "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", "r_loops": 1, "r_filtered": 100, "r_unpack_time_ms": 0.003393764, "r_other_time_ms": 0.013782408, "r_effective_rows": 101 } } ] } } } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "9", "used_key_parts": ["bin_number"], "ref": ["a.bin_number"], "loops": 204525, "r_loops": 101, "rows": 14009, "r_rows": 37.3960396, "cost": 320145.7906, "r_table_time_ms": 0.275330335, "r_other_time_ms": 0.589370246, "filtered": 100, "r_filtered": 0, "attached_condition": "trigcond(b.canonical = 45 and a.bin_number = b.bin_number)", "materialized": { "query_block": { "select_id": 3, "cost": 11072.79904, "r_loops": 1, "r_total_time_ms": 4552.138867, "filesort": { "sort_key": "a.canonical, floor((a.duration - b.val_min) / b.bin_width)", "r_loops": 1, "r_total_time_ms": 0.530544404, "r_used_priority_queue": false, "r_output_rows": 3777, "r_buffer_size": "110Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "possible_keys": ["canonical_idx"], "loops": 1, "r_loops": 1, "rows": 14149445, "r_rows": 14149445, "cost": 2245.44139, "r_table_time_ms": 271.380485, "r_other_time_ms": 649.6528994, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["canonical"], "ref": ["test.a.canonical"], "loops": 14149445, "r_loops": 14149445, "rows": 4, "r_rows": 1, "cost": 8827.357654, "r_table_time_ms": 853.3180486, "r_other_time_ms": 2776.641659, "filtered": 100, "r_filtered": 98.74845268, "attached_condition": "a.duration < b.val_max", "materialized": { "query_block": { "select_id": 2, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.033629475, "filesort": { "sort_key": "can_summary.canonical", "r_loops": 1, "r_total_time_ms": 0.00258295, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.00875057, "r_other_time_ms": 0.008764513, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 100 } } ] } } } } } } ] } } } } } } ] } } } } ] } } 1 row in set (4.544 sec) -------------- ALTER TABLE replay_results ENGINE=InnoDB -------------- Query OK, 14149445 rows affected (39.369 sec) Records: 14149445 Duplicates: 0 Warnings: 0 -------------- ALTER TABLE canonicals ENGINE=InnoDB -------------- Query OK, 46 rows affected (0.001 sec) Records: 46 Duplicates: 0 Warnings: 0 -------------- ANALYZE FORMAT=JSON WITH hist_limits AS ( SELECT canonical, dur_avg + dur_stddev * 3 val_max, dur_min val_min, (dur_avg + dur_stddev * 3 - dur_min) / 100 bin_width FROM can_summary GROUP BY canonical ), hist_data AS ( SELECT a.canonical, FLOOR((duration - b.val_min) / b.bin_width) bin_number, b.val_min + FLOOR((duration - b.val_min) / b.bin_width) * b.bin_width bin_value, COUNT(duration) cnt FROM replay_results a JOIN hist_limits b ON (a.canonical = b.canonical) WHERE a.duration < b.val_max GROUP BY 1, 2 ), hist_empty_range AS ( SELECT canonical, seq bin_number, val_min + seq * bin_width bin_value FROM hist_limits JOIN seq_0_to_100 GROUP BY 1, 2 ), json_histogram AS( SELECT a.canonical, JSON_OBJECT( 'id', a.canonical, 'sql', cs.canonical_sql, 'dur_min', cs.dur_min, 'dur_max', cs.dur_max, 'dur_sum', cs.dur_sum, 'dur_avg', cs.dur_avg, 'dur_stddev', cs.dur_stddev, 'rr_min', cs.rr_min, 'rr_max', cs.rr_max, 'rr_sum', cs.rr_sum, 'rr_avg', cs.rr_avg, 'rr_stddev', cs.rr_stddev, 'bins', JSON_ARRAYAGG(a.bin_value ORDER BY a.bin_number), 'counts', JSON_ARRAYAGG(COALESCE(b.cnt, 0) ORDER BY a.bin_number) ) js FROM hist_empty_range a LEFT JOIN hist_data b ON (a.canonical = b.canonical AND a.bin_number = b.bin_number) JOIN can_summary cs ON (a.canonical = cs.canonical) GROUP BY 1 ) SELECT JSON_ARRAYAGG(js) FROM json_histogram WHERE canonical = 45 -------------- *************************** 1. row *************************** ANALYZE: { "query_optimization": { "r_total_time_ms": 0.469927479 }, "query_block": { "select_id": 1, "cost": 408.340072, "r_loops": 1, "r_total_time_ms": 21499.70912, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 2045250, "r_rows": 1, "cost": 408.340072, "r_table_time_ms": 0.023993015, "r_other_time_ms": 0.012428191, "filtered": 100, "r_filtered": 100, "attached_condition": "json_histogram.canonical = 45", "materialized": { "query_block": { "select_id": 5, "cost": 1679.97416, "r_loops": 1, "r_total_time_ms": 21499.63537, "nested_loop": [ { "table": { "table_name": "cs", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.027502253, "r_other_time_ms": 0.005865889, "r_engine_stats": { "pages_accessed": 1, "pages_read_count": 1, "pages_read_time_ms": 9.01 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "cs.canonical = 45" } }, { "block-nl-join": { "table": { "table_name": "", "access_type": "ALL", "loops": 45, "r_loops": 1, "rows": 4545, "r_rows": 101, "cost": 19.20924913, "r_table_time_ms": 0.003529615, "r_other_time_ms": 0.083686871, "filtered": 100, "r_filtered": 100, "attached_condition": "a.canonical = 45" }, "buffer_type": "flat", "buffer_size": "22Kb", "join_type": "BNL", "r_loops": 1, "r_filtered": 100, "r_unpack_time_ms": 0.008880344, "r_other_time_ms": 0.006600912, "r_effective_rows": 101, "materialized": { "query_block": { "select_id": 4, "cost": 0.448553265, "r_loops": 1, "r_total_time_ms": 0.076681267, "filesort": { "sort_key": "seq_0_to_100.seq", "r_loops": 1, "r_total_time_ms": 0.008412374, "r_used_priority_queue": false, "r_output_rows": 101, "r_buffer_size": "2Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 1, "cost": 0.014239777, "r_table_time_ms": 7.968714e-4, "r_other_time_ms": 0.00262299, "filtered": 100, "r_filtered": 100, "attached_condition": "hist_limits.canonical = 45", "materialized": { "query_block": { "select_id": 6, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.02332556, "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.010485169, "r_other_time_ms": 0.009851318, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 2.222222222, "attached_condition": "can_summary.canonical = 45" } } ] } } } }, { "block-nl-join": { "table": { "table_name": "seq_0_to_100", "access_type": "index", "key": "PRIMARY", "key_length": "8", "used_key_parts": ["seq"], "loops": 45, "r_loops": 1, "rows": 101, "r_rows": 101, "cost": 0.434313488, "r_table_time_ms": 0.002173611, "r_other_time_ms": 0.004435882, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true }, "buffer_type": "flat", "buffer_size": "1Kb", "join_type": "BNL", "r_loops": 1, "r_filtered": 100, "r_unpack_time_ms": 0.003833849, "r_other_time_ms": 0.01585413, "r_effective_rows": 101 } } ] } } } } } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "9", "used_key_parts": ["bin_number"], "ref": ["a.bin_number"], "loops": 204525, "r_loops": 101, "rows": 10, "r_rows": 37.3960396, "cost": 261.4259002, "r_table_time_ms": 0.241161032, "r_other_time_ms": 0.503580192, "filtered": 100, "r_filtered": 0, "attached_condition": "trigcond(b.canonical = 45 and a.bin_number = b.bin_number)", "materialized": { "query_block": { "select_id": 3, "cost": 77.74180378, "r_loops": 1, "r_total_time_ms": 21499.16699, "filesort": { "sort_key": "a.canonical, floor((a.duration - b.val_min) / b.bin_width)", "r_loops": 1, "r_total_time_ms": 0.318085758, "r_used_priority_queue": false, "r_output_rows": 3777, "r_buffer_size": "110Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.014239777, "r_table_time_ms": 0.02702892, "r_other_time_ms": 0.096835071, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 2, "cost": 0.047098746, "r_loops": 1, "r_total_time_ms": 0.031323231, "filesort": { "sort_key": "can_summary.canonical", "r_loops": 1, "r_total_time_ms": 0.002752764, "r_used_priority_queue": false, "r_output_rows": 45, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "can_summary", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 45, "r_rows": 45, "cost": 0.0181898, "r_table_time_ms": 0.011483314, "r_other_time_ms": 0.009265731, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 100 } } ] } } } } } }, { "table": { "table_name": "a", "access_type": "ref", "possible_keys": ["canonical_idx"], "key": "canonical_idx", "key_length": "4", "used_key_parts": ["canonical"], "ref": ["b.canonical"], "loops": 45, "r_loops": 45, "rows": 952, "r_rows": 314432.1111, "cost": 77.727564, "r_table_time_ms": 19122.05761, "r_other_time_ms": 2376.130983, "r_engine_stats": { "pages_accessed": 42477300, "pages_read_count": 1922606, "pages_read_time_ms": 15867966.82 }, "filtered": 100, "r_filtered": 98.74845268, "attached_condition": "a.duration < b.val_max" } } ] } } } } } } ] } } } } ] } } 1 row in set (21.456 sec) Bye