SET optimizer_trace_max_mem_size=1048576; SET end_markers_in_json=off; SET optimizer_trace="enabled=on,one_line=off"; # # WL#1393 - Optimizing filesort with small limit # WL#5834 - Add optimizer traces for sorting # CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)) charset latin1; INSERT INTO t1(f1, f2) VALUES (0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), (6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), (11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), (16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), (21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), (26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), (31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), (36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), (41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), (46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), (51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), (56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), (61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), (66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), (71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), (76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), (81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), (86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), (91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), (96,"96"),(97,"97"),(98,"98"),(99,"99"); SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100; f0 f1 f2 1 0 0 2 1 1 3 2 2 4 3 3 5 4 4 6 5 5 7 6 6 8 7 7 9 8 8 10 9 9 11 10 10 12 11 11 13 12 12 14 13 13 15 14 14 16 15 15 17 16 16 18 17 17 19 18 18 20 19 19 21 20 20 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 31 30 30 32 31 31 33 32 32 34 33 33 35 34 34 36 35 35 37 36 36 38 37 37 39 38 38 40 39 39 41 40 40 42 41 41 43 42 42 44 43 43 45 44 44 46 45 45 47 46 46 48 47 47 49 48 48 50 49 49 51 50 50 52 51 51 53 52 52 54 53 53 55 54 54 56 55 55 57 56 56 58 57 57 59 58 58 60 59 59 61 60 60 62 61 61 63 62 62 64 63 63 65 64 64 66 65 65 67 66 66 68 67 67 69 68 68 70 69 69 71 70 70 72 71 71 73 72 72 74 73 73 75 74 74 76 75 75 77 76 76 78 77 77 79 78 78 80 79 79 81 80 80 82 81 81 83 82 82 84 83 83 85 84 84 86 85 85 87 86 86 88 87 87 89 88 88 90 89 89 91 90 90 92 91 91 93 92 92 94 93 93 95 94 94 96 95 95 97 96 96 98 97 97 99 98 98 100 99 99 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 100" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true, "use_tmp_table": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "sort_cost": 100, "new_cost_for_plan": 110.622, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 100, "chosen": false, "cause": "sort_is_cheaper" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 17, "row_size": 231, "max_rows_per_buffer": 100, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; f0 f1 f2 1 0 0 2 1 1 3 2 2 4 3 3 5 4 4 6 5 5 7 6 6 8 7 7 9 8 8 10 9 9 11 10 10 12 11 11 13 12 12 14 13 13 15 14 14 16 15 15 17 16 16 18 17 17 19 18 18 20 19 19 21 20 20 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 30" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 17, "row_size": 227, "max_rows_per_buffer": 31, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; f0 f1 f2 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ], "empty_result": { "cause": "Zero limit" } } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; f0 f1 f2 100 99 99 99 98 98 98 97 97 97 96 96 96 95 95 95 94 94 94 93 93 93 92 92 92 91 91 91 90 90 10 9 9 90 89 89 89 88 88 88 87 87 87 86 86 86 85 85 85 84 84 84 83 83 83 82 82 82 81 81 81 80 80 9 8 8 80 79 79 79 78 78 78 77 77 77 76 76 76 75 75 75 74 74 74 73 73 73 72 72 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f2` desc,`t1`.`f0` limit 30" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2` desc,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2` desc,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "desc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 31, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; f0 f1 f2 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f2` desc,`t1`.`f0` limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ], "empty_result": { "cause": "Zero limit" } } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; f0 f1 f2 12 11 11 13 12 12 14 13 13 15 14 14 16 15 15 17 16 16 18 17 17 19 18 18 20 19 19 21 20 20 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 31 30 30 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 20" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 20, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 21, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; f0 f1 f2 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ], "empty_result": { "cause": "Zero limit" } } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; f0 f1 f2 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 31 30 30 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 10,10" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 20, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 21, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; f0 f1 f2 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 10,0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 10, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 11, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT CONCAT("hello ", f2) AS foo FROM t1 ORDER BY foo LIMIT 2; foo hello 0 hello 1 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT CONCAT("hello ", f2) AS foo FROM t1 ORDER BY foo LIMIT 2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select concat('hello ',`t1`.`f2`) AS `foo` from `t1` order by `foo` limit 2" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`foo`", "items": [ { "item": "concat('hello ',`t1`.`f2`)" } ], "resulting_clause_is_simple": true, "resulting_clause": "`foo`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "concat('hello ',`t1`.`f2`)" } ], "filesort_priority_queue_optimization": { "limit": 2, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 207, "row_size": 409, "max_rows_per_buffer": 3, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * from t1 ORDER BY rand(2) LIMIT 2; SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * from t1 ORDER BY rand(2) LIMIT 2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by rand(2) limit 2" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "rand(2)", "items": [ { "item": "rand(2)" } ], "resulting_clause_is_simple": false, "resulting_clause": "rand(2)" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 1, "write_method": "write_all_rows" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "", "filesort_information": [ { "direction": "asc", "expression": "`rand(2)`" } ], "filesort_priority_queue_optimization": { "limit": 2, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 262144, "key_size": 8, "row_size": 226, "max_rows_per_buffer": 3, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 set sort_buffer_size= 32768; SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; f0 f1 f2 1 0 0 2 1 1 3 2 2 4 3 3 5 4 4 6 5 5 7 6 6 8 7 7 9 8 8 10 9 9 11 10 10 12 11 11 13 12 12 14 13 13 15 14 14 16 15 15 17 16 16 18 17 17 19 18 18 20 19 19 21 20 20 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 30" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 17, "row_size": 227, "max_rows_per_buffer": 31, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; f0 f1 f2 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ], "empty_result": { "cause": "Zero limit" } } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; f0 f1 f2 100 99 99 99 98 98 98 97 97 97 96 96 96 95 95 95 94 94 94 93 93 93 92 92 92 91 91 91 90 90 10 9 9 90 89 89 89 88 88 88 87 87 87 86 86 86 85 85 85 84 84 84 83 83 83 82 82 82 81 81 81 80 80 9 8 8 80 79 79 79 78 78 78 77 77 77 76 76 76 75 75 75 74 74 74 73 73 73 72 72 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f2` desc,`t1`.`f0` limit 30" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2` desc,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2` desc,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "desc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 31, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; f0 f1 f2 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f2` desc,`t1`.`f0` limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ], "empty_result": { "cause": "Zero limit" } } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; f0 f1 f2 12 11 11 13 12 12 14 13 13 15 14 14 16 15 15 17 16 16 18 17 17 19 18 18 20 19 19 21 20 20 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 31 30 30 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 20" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 20, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 21, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; f0 f1 f2 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ], "empty_result": { "cause": "Zero limit" } } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; f0 f1 f2 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 31 30 30 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 10,10" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 20, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 21, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; f0 f1 f2 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 10,0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 10, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 11, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 set sort_buffer_size= 32768; set optimizer_trace_limit=1; set optimizer_trace_offset=-2; SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY f1, f0 LIMIT 30; f0 f1 f2 1 0 0 2 1 1 3 2 2 4 3 3 5 4 4 6 5 5 7 6 6 8 7 7 9 8 8 10 9 9 11 10 10 12 11 11 13 12 12 14 13 13 15 14 14 16 15 15 17 16 16 18 17 17 19 18 18 20 19 19 21 20 20 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. SELECT FOUND_ROWS(); FOUND_ROWS() 100 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY f1, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 30" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 17, "row_size": 231, "max_rows_per_buffer": 100, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY f1, f0 LIMIT 0; f0 f1 f2 Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. SELECT FOUND_ROWS(); FOUND_ROWS() 100 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY f1, f0 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 17, "row_size": 231, "max_rows_per_buffer": 100, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; f0 f1 f2 12 11 11 13 12 12 14 13 13 15 14 14 16 15 15 17 16 16 18 17 17 19 18 18 20 19 19 21 20 20 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 31 30 30 Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. SELECT FOUND_ROWS(); FOUND_ROWS() 89 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 20" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 209, "row_size": 423, "max_rows_per_buffer": 76, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; f0 f1 f2 Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. SELECT FOUND_ROWS(); FOUND_ROWS() 89 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 209, "row_size": 423, "max_rows_per_buffer": 76, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; f0 f1 f2 22 21 21 23 22 22 24 23 23 25 24 24 26 25 25 27 26 26 28 27 27 29 28 28 30 29 29 31 30 30 Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. SELECT FOUND_ROWS(); FOUND_ROWS() 89 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 10,10" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 209, "row_size": 423, "max_rows_per_buffer": 76, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; f0 f1 f2 Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. SELECT FOUND_ROWS(); FOUND_ROWS() 89 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 10,0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 100, "cost": 0.62207 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 33.33, "cost": 10.6221, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 33.33, "cost_for_plan": 10.6221, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 209, "row_size": 423, "max_rows_per_buffer": 76, "num_rows_estimate": 100, "num_rows_found": 89, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 set sort_buffer_size= 327680; set optimizer_trace_limit=1; set optimizer_trace_offset=-1; CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)) charset latin1; INSERT INTO tmp SELECT f1, f2 FROM t1; INSERT INTO t1(f1, f2) SELECT * FROM tmp; INSERT INTO tmp SELECT f1, f2 FROM t1; INSERT INTO t1(f1, f2) SELECT * FROM tmp; SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 ORDER BY tmp.f1, f0 LIMIT 30; f0 f1 f2 f1 f2 1 0 0 0 0 1 0 0 0 0 1 0 0 0 0 101 0 0 0 0 101 0 0 0 0 101 0 0 0 0 201 0 0 0 0 201 0 0 0 0 201 0 0 0 0 301 0 0 0 0 301 0 0 0 0 301 0 0 0 0 401 0 0 0 0 401 0 0 0 0 401 0 0 0 0 2 1 1 1 1 2 1 1 1 1 2 1 1 1 1 102 1 1 1 1 102 1 1 1 1 102 1 1 1 1 202 1 1 1 1 202 1 1 1 1 202 1 1 1 1 302 1 1 1 1 302 1 1 1 1 302 1 1 1 1 402 1 1 1 1 402 1 1 1 1 402 1 1 1 1 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 ORDER BY tmp.f1, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`tmp`.`f1` AS `f1`,`tmp`.`f2` AS `f2` from (`t1` join `tmp` on((`t1`.`f2` = `tmp`.`f2`))) order by `tmp`.`f1`,`t1`.`f0` limit 30" }, { "transformations_to_nested_joins": { "transformations": [ "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`tmp`.`f1` AS `f1`,`tmp`.`f2` AS `f2` from `t1` join `tmp` where (`t1`.`f2` = `tmp`.`f2`) order by `tmp`.`f1`,`t1`.`f0` limit 30" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f2` = `tmp`.`f2`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`tmp`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } }, { "table": "`tmp`", "table_scan": { "rows": 300, "cost": 0.866211 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 300, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 300, "cost": 30.8662, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 300, "cost_for_plan": 30.8662, "rest_of_plan": [ { "plan_prefix": [ "`tmp`" ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 500, "cost": 15001.1, "chosen": true } ] }, "condition_filtering_pct": 10, "rows_for_plan": 15000, "cost_for_plan": 15032, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "pruned_by_heuristic": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f2` = `tmp`.`f2`)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`tmp`", "attached": null }, { "table": "`t1`", "attached": "(`t1`.`f2` = `tmp`.`f2`)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`tmp`.`f1`,`t1`.`f0`", "items": [ { "item": "`tmp`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`tmp`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f2` = `tmp`.`f2`)", "final_table_condition ": "(`t1`.`f2` = `tmp`.`f2`)" } ] }, { "refine_plan": [ { "table": "`tmp`" }, { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 2, "write_method": "write_all_rows" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "", "filesort_information": [ { "direction": "asc", "expression": "`tmp`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 252, "max_rows_per_buffer": 31, "num_rows_estimate": 15000, "num_rows_found": 1500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; f0 f1 f2 f1 f2 3 2 2 2 2 3 2 2 2 2 3 2 2 2 2 103 2 2 2 2 103 2 2 2 2 103 2 2 2 2 203 2 2 2 2 203 2 2 2 2 203 2 2 2 2 303 2 2 2 2 303 2 2 2 2 303 2 2 2 2 403 2 2 2 2 403 2 2 2 2 403 2 2 2 2 4 3 3 3 3 4 3 3 3 3 4 3 3 3 3 104 3 3 3 3 104 3 3 3 3 104 3 3 3 3 204 3 3 3 3 204 3 3 3 3 204 3 3 3 3 304 3 3 3 3 304 3 3 3 3 304 3 3 3 3 404 3 3 3 3 404 3 3 3 3 404 3 3 3 3 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`tmp`.`f1` AS `f1`,`tmp`.`f2` AS `f2` from (`t1` join `tmp` on((`t1`.`f2` = `tmp`.`f2`))) order by `tmp`.`f1`,`t1`.`f0` limit 30,30" }, { "transformations_to_nested_joins": { "transformations": [ "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`tmp`.`f1` AS `f1`,`tmp`.`f2` AS `f2` from `t1` join `tmp` where (`t1`.`f2` = `tmp`.`f2`) order by `tmp`.`f1`,`t1`.`f0` limit 30,30" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f2` = `tmp`.`f2`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`tmp`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } }, { "table": "`tmp`", "table_scan": { "rows": 300, "cost": 0.866211 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 300, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 300, "cost": 30.8662, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 300, "cost_for_plan": 30.8662, "rest_of_plan": [ { "plan_prefix": [ "`tmp`" ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 500, "cost": 15001.1, "chosen": true } ] }, "condition_filtering_pct": 10, "rows_for_plan": 15000, "cost_for_plan": 15032, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "pruned_by_heuristic": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f2` = `tmp`.`f2`)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`tmp`", "attached": null }, { "table": "`t1`", "attached": "(`t1`.`f2` = `tmp`.`f2`)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`tmp`.`f1`,`t1`.`f0`", "items": [ { "item": "`tmp`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`tmp`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f2` = `tmp`.`f2`)", "final_table_condition ": "(`t1`.`f2` = `tmp`.`f2`)" } ] }, { "refine_plan": [ { "table": "`tmp`" }, { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 2, "write_method": "write_all_rows" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "", "filesort_information": [ { "direction": "asc", "expression": "`tmp`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 60, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 252, "max_rows_per_buffer": 61, "num_rows_estimate": 15000, "num_rows_found": 1500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 set optimizer_trace_limit=2; set optimizer_trace_offset=-2; SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; f0 f1 f2 f1 f2 3 2 2 2 2 3 2 2 2 2 3 2 2 2 2 103 2 2 2 2 103 2 2 2 2 103 2 2 2 2 203 2 2 2 2 203 2 2 2 2 203 2 2 2 2 303 2 2 2 2 303 2 2 2 2 303 2 2 2 2 403 2 2 2 2 403 2 2 2 2 403 2 2 2 2 4 3 3 3 3 4 3 3 3 3 4 3 3 3 3 104 3 3 3 3 104 3 3 3 3 104 3 3 3 3 204 3 3 3 3 204 3 3 3 3 204 3 3 3 3 304 3 3 3 3 304 3 3 3 3 304 3 3 3 3 404 3 3 3 3 404 3 3 3 3 404 3 3 3 3 Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. SELECT FOUND_ROWS(); FOUND_ROWS() 1500 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`tmp`.`f1` AS `f1`,`tmp`.`f2` AS `f2` from (`t1` join `tmp` on((`t1`.`f2` = `tmp`.`f2`))) order by `tmp`.`f1`,`t1`.`f0` limit 30,30" }, { "transformations_to_nested_joins": { "transformations": [ "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`tmp`.`f1` AS `f1`,`tmp`.`f2` AS `f2` from `t1` join `tmp` where (`t1`.`f2` = `tmp`.`f2`) order by `tmp`.`f1`,`t1`.`f0` limit 30,30" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f2` = `tmp`.`f2`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f2` = `tmp`.`f2`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`tmp`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } }, { "table": "`tmp`", "table_scan": { "rows": 300, "cost": 0.866211 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 300, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 300, "cost": 30.8662, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 300, "cost_for_plan": 30.8662, "rest_of_plan": [ { "plan_prefix": [ "`tmp`" ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 500, "cost": 15001.1, "chosen": true } ] }, "condition_filtering_pct": 10, "rows_for_plan": 15000, "cost_for_plan": 15032, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "pruned_by_heuristic": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f2` = `tmp`.`f2`)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`tmp`", "attached": null }, { "table": "`t1`", "attached": "(`t1`.`f2` = `tmp`.`f2`)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`tmp`.`f1`,`t1`.`f0`", "items": [ { "item": "`tmp`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`tmp`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f2` = `tmp`.`f2`)", "final_table_condition ": "(`t1`.`f2` = `tmp`.`f2`)" } ] }, { "refine_plan": [ { "table": "`tmp`" }, { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 2, "write_method": "write_all_rows" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "", "filesort_information": [ { "direction": "asc", "expression": "`tmp`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 256, "max_rows_per_buffer": 1241, "num_rows_estimate": 15000, "num_rows_found": 1500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::stable_sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT FOUND_ROWS() { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select found_rows() AS `FOUND_ROWS()`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 WHERE t1.f2>20 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; f0 f1 f2 f1 f2 24 23 23 23 23 24 23 23 23 23 24 23 23 23 23 124 23 23 23 23 124 23 23 23 23 124 23 23 23 23 224 23 23 23 23 224 23 23 23 23 224 23 23 23 23 324 23 23 23 23 324 23 23 23 23 324 23 23 23 23 424 23 23 23 23 424 23 23 23 23 424 23 23 23 23 25 24 24 24 24 25 24 24 24 24 25 24 24 24 24 125 24 24 24 24 125 24 24 24 24 125 24 24 24 24 225 24 24 24 24 225 24 24 24 24 225 24 24 24 24 325 24 24 24 24 325 24 24 24 24 325 24 24 24 24 425 24 24 24 24 425 24 24 24 24 425 24 24 24 24 Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. SELECT FOUND_ROWS(); FOUND_ROWS() 1185 Warnings: Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 WHERE t1.f2>20 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`tmp`.`f1` AS `f1`,`tmp`.`f2` AS `f2` from (`t1` join `tmp` on((`t1`.`f2` = `tmp`.`f2`))) where (`t1`.`f2` > 20) order by `tmp`.`f1`,`t1`.`f0` limit 30,30" }, { "transformations_to_nested_joins": { "transformations": [ "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,`tmp`.`f1` AS `f1`,`tmp`.`f2` AS `f2` from `t1` join `tmp` where ((`t1`.`f2` > 20) and (`t1`.`f2` = `tmp`.`f2`)) order by `tmp`.`f1`,`t1`.`f0` limit 30,30" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`t1`.`f2` > 20) and (`t1`.`f2` = `tmp`.`f2`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`t1`.`f2` > 20) and (`t1`.`f2` = `tmp`.`f2`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`t1`.`f2` > 20) and (`t1`.`f2` = `tmp`.`f2`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`t1`.`f2` > 20) and (`t1`.`f2` = `tmp`.`f2`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`tmp`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } }, { "table": "`tmp`", "table_scan": { "rows": 300, "cost": 0.866211 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 300, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 300, "cost": 30.8662, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 300, "cost_for_plan": 30.8662, "rest_of_plan": [ { "plan_prefix": [ "`tmp`" ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 166.65, "cost": 5034.97, "chosen": true } ] }, "condition_filtering_pct": 10, "rows_for_plan": 4999.5, "cost_for_plan": 5065.84, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 166.65, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 166.65, "cost_for_plan": 51.1104, "rest_of_plan": [ { "plan_prefix": [ "`t1`" ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 300, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 300, "cost": 5000.48, "chosen": true } ] }, "condition_filtering_pct": 10, "rows_for_plan": 4999.5, "cost_for_plan": 5051.59, "chosen": true } ] } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`f2` > 20) and (`t1`.`f2` = `tmp`.`f2`))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f2` > 20)" }, { "table": "`tmp`", "attached": "(`t1`.`f2` = `tmp`.`f2`)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`tmp`.`f1`,`t1`.`f0`", "items": [ { "item": "`tmp`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`tmp`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f2` > 20)", "final_table_condition ": "(`t1`.`f2` > 20)" }, { "table": "`tmp`", "original_table_condition": "(`t1`.`f2` = `tmp`.`f2`)", "final_table_condition ": "(`t1`.`f2` = `tmp`.`f2`)" } ] }, { "refine_plan": [ { "table": "`t1`" }, { "table": "`tmp`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 2, "write_method": "write_all_rows" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "", "filesort_information": [ { "direction": "asc", "expression": "`tmp`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 256, "max_rows_per_buffer": 1241, "num_rows_estimate": 4999, "num_rows_found": 1185, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::stable_sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT FOUND_ROWS() { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select found_rows() AS `FOUND_ROWS()`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 DROP TABLE tmp; set optimizer_trace_limit=1; set optimizer_trace_offset=-1; CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30; SELECT * FROM v1; f0 f1 f2 1 0 0 101 0 0 201 0 0 301 0 0 401 0 0 2 1 1 102 1 1 202 1 1 302 1 1 402 1 1 3 2 2 103 2 2 203 2 2 303 2 2 403 2 2 4 3 3 104 3 3 204 3 3 304 3 3 404 3 3 5 4 4 105 4 4 205 4 4 305 4 4 405 4 4 6 5 5 106 5 5 206 5 5 306 5 5 406 5 5 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM v1 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 30" } ] } }, { "view": { "table": "`v1`", "select#": 2, "materialized": true } }, { "expanded_query": "/* select#1 */ select `v1`.`f0` AS `f0`,`v1`.`f1` AS `f1`,`v1`.`f2` AS `f2` from `v1`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "table_dependencies": [ { "table": "`v1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`v1`", "table_scan": { "rows": 30, "cost": 2.875 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 30, "access_type": "scan", "resulting_rows": 30, "cost": 5.875, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 30, "cost_for_plan": 5.875, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`v1`", "attached": null } ] } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`v1`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "in_plan_at_position": 0, "columns": 3, "row_length": 210, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "materialize": { "select#": 2, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 227, "max_rows_per_buffer": 31, "num_rows_estimate": 500, "num_rows_found": 500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } } ] } 0 0 drop view v1; CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100; SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; f0 f1 f2 1 0 0 101 0 0 201 0 0 301 0 0 401 0 0 2 1 1 102 1 1 202 1 1 302 1 1 402 1 1 11 10 10 111 10 10 211 10 10 311 10 10 411 10 10 12 11 11 112 11 11 212 11 11 312 11 11 412 11 11 13 12 12 113 12 12 213 12 12 313 12 12 413 12 12 14 13 13 114 13 13 214 13 13 314 13 13 414 13 13 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 100" } ] } }, { "view": { "table": "`v1`", "select#": 2, "materialized": true } }, { "expanded_query": "/* select#1 */ select `v1`.`f0` AS `f0`,`v1`.`f1` AS `f1`,`v1`.`f2` AS `f2` from `v1` order by `v1`.`f2`,`v1`.`f0` limit 30" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`v1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`v1`", "table_scan": { "rows": 100, "cost": 3.75 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 13.75, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 13.75, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`v1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`v1`.`f2`,`v1`.`f0`", "items": [ { "item": "`v1`.`f2`" }, { "item": "`v1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`v1`.`f2`,`v1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`v1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "v1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "in_plan_at_position": 0, "columns": 3, "row_length": 210, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "materialize": { "select#": 2, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 100, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 227, "max_rows_per_buffer": 101, "num_rows_estimate": 500, "num_rows_found": 500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::stable_sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } }, { "sorting_table": "v1", "filesort_information": [ { "direction": "asc", "expression": "`v1`.`f2`" }, { "direction": "asc", "expression": "`v1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 31, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 EXPLAIN FORMAT=tree SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; EXPLAIN -> Limit: 30 row(s) (cost=89.6..89.6 rows=30) -> Sort: v1.f2, v1.f0, limit input to 30 row(s) per chunk (cost=89.6..89.6 rows=30) -> Table scan on v1 (cost=61.1..64.9 rows=100) -> Materialize (cost=61.1..61.1 rows=100) -> Limit: 100 row(s) (cost=51.1 rows=100) -> Sort: t1.f1, t1.f0, limit input to 100 row(s) per chunk (cost=51.1 rows=500) -> Table scan on t1 (cost=51.1 rows=500) CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100; SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0 LIMIT 30; f0 f1 f2 f0 f1 f2 1 0 0 1 0 0 1 0 0 101 0 0 1 0 0 201 0 0 1 0 0 301 0 0 1 0 0 401 0 0 101 0 0 1 0 0 101 0 0 101 0 0 101 0 0 201 0 0 101 0 0 301 0 0 101 0 0 401 0 0 201 0 0 1 0 0 201 0 0 101 0 0 201 0 0 201 0 0 201 0 0 301 0 0 201 0 0 401 0 0 301 0 0 1 0 0 301 0 0 101 0 0 301 0 0 201 0 0 301 0 0 301 0 0 301 0 0 401 0 0 401 0 0 1 0 0 401 0 0 101 0 0 401 0 0 201 0 0 401 0 0 301 0 0 401 0 0 401 0 0 2 1 1 2 1 1 2 1 1 102 1 1 2 1 1 202 1 1 2 1 1 302 1 1 2 1 1 402 1 1 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 100" } ] } }, { "view": { "table": "`v1`", "select#": 2, "materialized": true } }, { "join_preparation": { "select#": 3, "steps": [ { "expanded_query": "/* select#3 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f2`,`t1`.`f0` limit 100" } ] } }, { "view": { "table": "`v2`", "select#": 3, "materialized": true } }, { "expanded_query": "/* select#1 */ select `v1`.`f0` AS `f0`,`v1`.`f1` AS `f1`,`v1`.`f2` AS `f2`,`v2`.`f0` AS `f0`,`v2`.`f1` AS `f1`,`v2`.`f2` AS `f2` from (`v1` join `v2` on((`v1`.`f1` = `v2`.`f1`))) order by `v1`.`f2`,`v1`.`f0`,`v2`.`f0` limit 30" }, { "transformations_to_nested_joins": { "transformations": [ "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `v1`.`f0` AS `f0`,`v1`.`f1` AS `f1`,`v1`.`f2` AS `f2`,`v2`.`f0` AS `f0`,`v2`.`f1` AS `f1`,`v2`.`f2` AS `f2` from `v1` join `v2` where (`v1`.`f1` = `v2`.`f1`) order by `v1`.`f2`,`v1`.`f0`,`v2`.`f0` limit 30" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_optimization": { "select#": 3, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "condition_processing": { "condition": "WHERE", "original_condition": "(`v1`.`f1` = `v2`.`f1`)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(`v1`.`f1`, `v2`.`f1`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(`v1`.`f1`, `v2`.`f1`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(`v1`.`f1`, `v2`.`f1`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`v1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`v2`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`v1`", "field": "f1", "equals": "`v2`.`f1`", "null_rejecting": true }, { "table": "`v1`", "field": "f1", "equals": "`v2`.`f1`", "null_rejecting": true }, { "table": "`v2`", "field": "f1", "equals": "`v1`.`f1`", "null_rejecting": true }, { "table": "`v2`", "field": "f1", "equals": "`v1`.`f1`", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`v1`", "table_scan": { "rows": 100, "cost": 3.75 } }, { "table": "`v2`", "table_scan": { "rows": 100, "cost": 3.75 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "", "usable": false, "chosen": false }, { "access_type": "ref", "index": "", "usable": false, "chosen": false }, { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 100, "cost": 13.75, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 13.75, "rest_of_plan": [ { "plan_prefix": [ "`v1`" ], "table": "`v2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "", "rows": 10, "cost": 350, "chosen": true }, { "access_type": "ref", "index": "", "rows": 10, "cost": 350, "chosen": false }, { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 100, "cost": 1004.05, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1000, "cost_for_plan": 363.75, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`v2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "", "usable": false, "chosen": false }, { "access_type": "ref", "index": "", "usable": false, "chosen": false }, { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 100, "cost": 13.75, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 13.75, "rest_of_plan": [ { "plan_prefix": [ "`v2`" ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "", "rows": 10, "cost": 350, "chosen": true }, { "access_type": "ref", "index": "", "rows": 10, "cost": 350, "chosen": false }, { "rows_to_scan": 100, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 100, "cost": 1004.05, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1000, "cost_for_plan": 363.75, "pruned_by_cost": true } ] } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`v2`.`f1` = `v1`.`f1`)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`v1`", "attached": "(`v1`.`f1` is not null)" }, { "table": "`v2`", "attached": "(`v2`.`f1` = `v1`.`f1`)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`v1`.`f2`,`v1`.`f0`,`v2`.`f0`", "items": [ { "item": "`v1`.`f2`" }, { "item": "`v1`.`f0`" }, { "item": "`v2`.`f0`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`v1`.`f2`,`v1`.`f0`,`v2`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`v1`", "original_table_condition": "(`v1`.`f1` is not null)", "final_table_condition ": "(`v1`.`f1` is not null)" }, { "table": "`v2`", "original_table_condition": "(`v2`.`f1` = `v1`.`f1`)", "final_table_condition ": null } ] }, { "refine_plan": [ { "table": "`v1`" }, { "table": "`v2`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 2, "write_method": "write_all_rows" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "in_plan_at_position": 0, "columns": 3, "row_length": 210, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "materialize": { "select#": 2, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 100, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 227, "max_rows_per_buffer": 101, "num_rows_estimate": 500, "num_rows_found": 500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::stable_sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } }, { "sorting_table": "", "filesort_information": [ { "direction": "asc", "expression": "`v1`.`f2`" }, { "direction": "asc", "expression": "`v1`.`f0`" }, { "direction": "asc", "expression": "`v2`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v2`", "in_plan_at_position": 1, "columns": 3, "row_length": 210, "key_length": 5, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "materialize": { "select#": 3, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 100, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 101, "num_rows_estimate": 500, "num_rows_found": 500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::stable_sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ], "filesort_summary": { "memory_available": 327680, "key_size": 217, "row_size": 636, "max_rows_per_buffer": 31, "num_rows_estimate": 10000, "num_rows_found": 325, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT floor(f1/10) f3, count(f2) FROM t1 GROUP BY 1 ORDER BY 2,1 LIMIT 5; f3 count(f2) 0 50 1 50 2 50 3 50 4 50 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT floor(f1/10) f3, count(f2) FROM t1 GROUP BY 1 ORDER BY 2,1 LIMIT 5 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select floor((`t1`.`f1` / 10)) AS `f3`,count(`t1`.`f2`) AS `count(f2)` from `t1` group by floor((`t1`.`f1` / 10)) order by count(`t1`.`f2`),floor((`t1`.`f1` / 10)) limit 5" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "count(`t1`.`f2`),floor((`t1`.`f1` / 10))", "items": [ { "item": "count(`t1`.`f2`)" }, { "item": "floor((`t1`.`f1` / 10))" } ], "resulting_clause_is_simple": false, "resulting_clause": "count(`t1`.`f2`),floor((`t1`.`f1` / 10))" }, "simplifying_group_by": { "original_clause": "floor((`t1`.`f1` / 10))", "items": [ { "item": "floor((`t1`.`f1` / 10))" } ], "resulting_clause_is_simple": true, "resulting_clause": "floor((`t1`.`f1` / 10))" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 1, "write_method": "continuously_update_group_row" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "temp_table_aggregate": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "", "in_plan_at_position": 1, "columns": 2, "row_length": 18, "key_length": 9, "unique_constraint": false, "makes_grouped_rows": true, "cannot_insert_duplicates": false, "location": "TempTable" } } } ] } }, { "sorting_table": "", "filesort_information": [ { "direction": "asc", "expression": "`count(f2)`" }, { "direction": "asc", "expression": "`f3`" } ], "filesort_priority_queue_optimization": { "limit": 5, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 34, "max_rows_per_buffer": 6, "num_rows_estimate": 18446744073709551615, "num_rows_found": 10, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT floor(f1/10) f3, count(f2) FROM t1 GROUP BY 1 ORDER BY 2,1 LIMIT 0; f3 count(f2) SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT floor(f1/10) f3, count(f2) FROM t1 GROUP BY 1 ORDER BY 2,1 LIMIT 0 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select floor((`t1`.`f1` / 10)) AS `f3`,count(`t1`.`f2`) AS `count(f2)` from `t1` group by floor((`t1`.`f1` / 10)) order by count(`t1`.`f2`),floor((`t1`.`f1` / 10)) limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ], "empty_result": { "cause": "Zero limit" } } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } 0 0 CREATE PROCEDURE wl1393_sp_test() BEGIN SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30; SELECT * FROM information_schema.OPTIMIZER_TRACE; SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15; SELECT * FROM information_schema.OPTIMIZER_TRACE; SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15; SELECT * FROM information_schema.OPTIMIZER_TRACE; SELECT FOUND_ROWS(); SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; SELECT * FROM information_schema.OPTIMIZER_TRACE; END| Warnings: Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead. CALL wl1393_sp_test()| f0 f1 f2 12 11 11 112 11 11 212 11 11 312 11 11 412 11 11 13 12 12 113 12 12 213 12 12 313 12 12 413 12 12 14 13 13 114 13 13 214 13 13 314 13 13 414 13 13 15 14 14 115 14 14 215 14 14 315 14 14 415 14 14 16 15 15 116 15 15 216 15 15 316 15 15 416 15 15 17 16 16 117 16 16 217 16 16 317 16 16 417 16 16 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 30" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 166.65, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 166.65, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 31, "num_rows_estimate": 500, "num_rows_found": 445, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 f0 f1 f2 15 14 14 115 14 14 215 14 14 315 14 14 415 14 14 16 15 15 116 15 15 216 15 15 316 15 15 416 15 15 17 16 16 117 16 16 217 16 16 317 16 16 417 16 16 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 15,15" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 166.65, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 166.65, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 31, "num_rows_estimate": 500, "num_rows_found": 445, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 f0 f1 f2 15 14 14 115 14 14 215 14 14 315 14 14 415 14 14 16 15 15 116 15 15 216 15 15 316 15 15 416 15 15 17 16 16 117 16 16 217 16 16 317 16 16 417 16 16 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select sql_calc_found_rows `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` > 10) order by `t1`.`f2`,`t1`.`f0` limit 15,15" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` > 10)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`f1` > 10)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`f1` > 10)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "filtering_effect": [ ], "final_filtering_effect": 0.3333, "access_type": "scan", "resulting_rows": 166.65, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 166.65, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f2`,`t1`.`f0`", "items": [ { "item": "`t1`.`f2`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` > 10)", "final_table_condition ": "(`t1`.`f1` > 10)" } ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f2`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 209, "row_size": 423, "max_rows_per_buffer": 500, "num_rows_estimate": 500, "num_rows_found": 445, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::stable_sort", "sort_mode": "" } } ] } } ] } 0 0 FOUND_ROWS() 1 f0 f1 f2 1 0 0 101 0 0 201 0 0 301 0 0 401 0 0 2 1 1 102 1 1 202 1 1 302 1 1 402 1 1 11 10 10 111 10 10 211 10 10 311 10 10 411 10 10 12 11 11 112 11 11 212 11 11 312 11 11 412 11 11 13 12 12 113 12 12 213 12 12 313 12 12 413 12 12 14 13 13 114 13 13 214 13 13 314 13 13 414 13 13 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1`,`t1`.`f0` limit 100" } ] } }, { "view": { "table": "`v1`", "select#": 2, "materialized": true } }, { "expanded_query": "/* select#1 */ select `v1`.`f0` AS `f0`,`v1`.`f1` AS `f1`,`v1`.`f2` AS `f2` from `v1` order by `v1`.`f2`,`v1`.`f0` limit 30" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`,`t1`.`f0`", "items": [ { "item": "`t1`.`f1`" }, { "item": "`t1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`,`t1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`v1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`v1`", "table_scan": { "rows": 100, "cost": 3.75 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 100, "access_type": "scan", "resulting_rows": 100, "cost": 13.75, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 100, "cost_for_plan": 13.75, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`v1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`v1`.`f2`,`v1`.`f0`", "items": [ { "item": "`v1`.`f2`" }, { "item": "`v1`.`f0`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`v1`.`f2`,`v1`.`f0`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`v1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "v1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "in_plan_at_position": 0, "columns": 3, "row_length": 210, "key_length": 0, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "materialize": { "select#": 2, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" }, { "direction": "asc", "expression": "`t1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 100, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 17, "row_size": 227, "max_rows_per_buffer": 101, "num_rows_estimate": 500, "num_rows_found": 500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::stable_sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } }, { "sorting_table": "v1", "filesort_information": [ { "direction": "asc", "expression": "`v1`.`f2`" }, { "direction": "asc", "expression": "`v1`.`f0`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 209, "row_size": 419, "max_rows_per_buffer": 31, "num_rows_estimate": 100, "num_rows_found": 100, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 DROP PROCEDURE wl1393_sp_test| SELECT d1.f1, d1.f2 FROM t1 LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1 ORDER BY d1.f2 DESC LIMIT 30; f1 f2 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 4 4 4 4 4 4 4 4 4 4 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT d1.f1, d1.f2 FROM t1 LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1 ORDER BY d1.f2 DESC LIMIT 30 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1` limit 30" } ] } }, { "derived": { "table": " `d1`", "select#": 2, "materialized": true } }, { "expanded_query": "/* select#1 */ select `d1`.`f1` AS `f1`,`d1`.`f2` AS `f2` from (`t1` left join (/* select#2 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1` limit 30) `d1` on((`t1`.`f1` = `d1`.`f1`))) order by `d1`.`f2` desc limit 30" }, { "transformations_to_nested_joins": { "transformations": [ "parenthesis_removal" ], "expanded_query": "/* select#1 */ select `d1`.`f1` AS `f1`,`d1`.`f2` AS `f2` from `t1` left join (/* select#2 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` order by `t1`.`f1` limit 30) `d1` on((`t1`.`f1` = `d1`.`f1`)) order by `d1`.`f2` desc limit 30" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`", "items": [ { "item": "`t1`.`f1`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "condition_processing": { "condition": "WHERE", "original_condition": null, "steps": [ { "transformation": "equality_propagation", "resulting_condition": null } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": " `d1`", "row_may_be_null": true, "map_bit": 1, "depends_on_map_bits": [ 0 ] } ] }, { "ref_optimizer_key_uses": [ { "table": " `d1`", "field": "f1", "equals": "`t1`.`f1`", "null_rejecting": true }, { "table": " `d1`", "field": "f1", "equals": "`t1`.`f1`", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } }, { "table": " `d1`", "table_scan": { "rows": 30, "cost": 2.875 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "rest_of_plan": [ { "plan_prefix": [ "`t1`" ], "table": " `d1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "", "rows": 3, "cost": 525, "chosen": true }, { "access_type": "ref", "index": "", "rows": 3, "cost": 525, "chosen": false }, { "rows_to_scan": 30, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 30, "cost": 1502.9, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 1500, "cost_for_plan": 576.11, "sort_cost": 1500, "new_cost_for_plan": 2076.11, "chosen": true } ] } ] }, { "condition_on_constant_tables": "true", "condition_value": true }, { "attaching_conditions_to_tables": { "original_condition": "true", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null }, { "table": " `d1`", "attached": "(is_not_null_compl(d1), (`d1`.`f1` = `t1`.`f1`), true)" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`d1`.`f2` desc", "items": [ { "item": "`d1`.`f2`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`d1`.`f2` desc" } } }, { "finalizing_table_conditions": [ { "table": " `d1`", "original_table_condition": "(is_not_null_compl(d1), (`d1`.`f1` = `t1`.`f1`), true)", "final_table_condition ": null } ] }, { "refine_plan": [ { "table": "`t1`" }, { "table": " `d1`" } ] }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 2, "write_method": "write_all_rows" }, { "adding_sort_to_table": "" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "", "filesort_information": [ { "direction": "desc", "expression": "`d1`.`f2`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ { "creating_tmp_table": { "tmp_table_info": { "table": "d1", "in_plan_at_position": 1, "columns": 3, "row_length": 210, "key_length": 5, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": false, "location": "TempTable" } } }, { "materialize": { "select#": 2, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" } ], "filesort_priority_queue_optimization": { "limit": 30, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 9, "row_size": 219, "max_rows_per_buffer": 31, "num_rows_estimate": 500, "num_rows_found": 500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ], "filesort_summary": { "memory_available": 327680, "key_size": 201, "row_size": 407, "max_rows_per_buffer": 31, "num_rows_estimate": 15000, "num_rows_found": 620, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } 0 0 SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1); f0 f1 f2 1 0 0 101 0 0 201 0 0 301 0 0 401 0 0 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1" } ] } }, { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ], "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1), `t1`.`f1`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1), `t1`.`f1`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1), `t1`.`f1`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1))" } ] } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1))", "final_table_condition ": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 1))" } ] }, { "refine_plan": [ { "table": "`t1`" } ] } ] } }, { "join_optimization": { "select#": 2, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`", "items": [ { "item": "`t1`.`f1`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_execution": { "select#": 2, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" } ], "filesort_priority_queue_optimization": { "limit": 1, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 9, "row_size": 14, "max_rows_per_buffer": 2, "num_rows_estimate": 500, "num_rows_found": 500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } } ] } } ] } 0 0 SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2); ERROR 21000: Subquery returns more than 1 row SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2) { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2" } ] } }, { "expanded_query": "/* select#1 */ select `t1`.`f0` AS `f0`,`t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1` where (`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ], "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2), `t1`.`f1`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2), `t1`.`f1`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2), `t1`.`f1`)" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2))" } ] } }, { "finalizing_table_conditions": [ { "table": "`t1`", "original_table_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2))", "final_table_condition ": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by `t1`.`f1` limit 2))" } ] }, { "refine_plan": [ { "table": "`t1`" } ] } ] } }, { "join_optimization": { "select#": 2, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 500, "cost": 1.11035 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 500, "access_type": "scan", "resulting_rows": 500, "cost": 51.1104, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 500, "cost_for_plan": 51.1104, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t1`.`f1`", "items": [ { "item": "`t1`.`f1`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t1`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t1" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_execution": { "select#": 2, "steps": [ { "sorting_table": "t1", "filesort_information": [ { "direction": "asc", "expression": "`t1`.`f1`" } ], "filesort_priority_queue_optimization": { "limit": 2, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 327680, "key_size": 9, "row_size": 14, "max_rows_per_buffer": 3, "num_rows_estimate": 500, "num_rows_found": 500, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "unpacked_addon_fields": "using_priority_queue", "sort_mode": "" } } ] } } ] } } ] } } ] } 0 0 DROP TABLE t1; DROP VIEW v1, v2; # end of WL#1393 - Optimizing filesort with small limit # end of WL#5834 - Add optimizer traces for sorting # WL#8741 Varlen keys for sorting JSON values # Note that JSON keys can't use the priority queue optimization for LIMIT, # since they can be of unbounded length. set sort_buffer_size= 32768; CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, j JSON); INSERT INTO t(j) VALUES (NULL), ('true'), ('false'), ('"abc"'), ('""'), ('"abcd"'), ('"bc"'), ('"abc\\u0000\\u0000"'), ('"abc\\u0000"'), ('0.0'), ('-0.0'), ('9223372036854776000'), ('1.0e-1'), ('1.0e-2') ; SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j, id; j tp NULL NULL 0.0 DOUBLE -0.0 DOUBLE 0.01 DOUBLE 0.1 DOUBLE 9223372036854776000 UNSIGNED INTEGER "" STRING "abc" STRING "abc\u0000" STRING "abc\u0000\u0000" STRING "abcd" STRING "bc" STRING false BOOLEAN true BOOLEAN SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j, id limit 2 offset 2; j tp -0.0 DOUBLE 0.01 DOUBLE SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j, id limit 2 offset 2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t`.`j` AS `j`,json_type(`t`.`j`) AS `tp` from `t` order by `t`.`j`,`t`.`id` limit 2,2" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t`", "table_scan": { "rows": 14, "cost": 0.518311 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 14, "access_type": "scan", "resulting_rows": 14, "cost": 1.91831, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 14, "cost_for_plan": 1.91831, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t`.`j`,`t`.`id`", "items": [ { "item": "`t`.`j`" }, { "item": "`t`.`id`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t`.`j`,`t`.`id`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t", "filesort_information": [ { "direction": "asc", "expression": "`t`.`j`" }, { "direction": "asc", "expression": "`t`.`id`" } ], "filesort_priority_queue_optimization": { "limit": 4, "usable": false, "cause": "contains records of unbounded length" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 4294967295, "row_size": 4294967295, "max_rows_per_buffer": 0, "num_rows_estimate": 15, "num_rows_found": 14, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j DESC, id; j tp true BOOLEAN false BOOLEAN "bc" STRING "abcd" STRING "abc\u0000\u0000" STRING "abc\u0000" STRING "abc" STRING "" STRING 9223372036854776000 UNSIGNED INTEGER 0.1 DOUBLE 0.01 DOUBLE 0.0 DOUBLE -0.0 DOUBLE NULL NULL SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j DESC, id limit 2 offset 2; j tp "bc" STRING "abcd" STRING SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j DESC, id limit 2 offset 2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t`.`j` AS `j`,json_type(`t`.`j`) AS `tp` from `t` order by `t`.`j` desc,`t`.`id` limit 2,2" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t`", "table_scan": { "rows": 14, "cost": 0.518311 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 14, "access_type": "scan", "resulting_rows": 14, "cost": 1.91831, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 14, "cost_for_plan": 1.91831, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`t`.`j` desc,`t`.`id`", "items": [ { "item": "`t`.`j`" }, { "item": "`t`.`id`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t`.`j` desc,`t`.`id`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t", "filesort_information": [ { "direction": "desc", "expression": "`t`.`j`" }, { "direction": "asc", "expression": "`t`.`id`" } ], "filesort_priority_queue_optimization": { "limit": 4, "usable": false, "cause": "contains records of unbounded length" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 4294967295, "row_size": 4294967295, "max_rows_per_buffer": 0, "num_rows_estimate": 15, "num_rows_found": 14, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 SELECT JSON_EXTRACT(j, '$') AS je, JSON_TYPE(j) AS tp FROM t ORDER BY je, id; je tp NULL NULL 0.0 DOUBLE -0.0 DOUBLE 0.01 DOUBLE 0.1 DOUBLE 9223372036854776000 UNSIGNED INTEGER "" STRING "abc" STRING "abc\u0000" STRING "abc\u0000\u0000" STRING "abcd" STRING "bc" STRING false BOOLEAN true BOOLEAN SELECT JSON_EXTRACT(j, '$') AS je, JSON_TYPE(j) AS tp FROM t ORDER BY je, id limit 2 offset 2; je tp -0.0 DOUBLE 0.01 DOUBLE SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT JSON_EXTRACT(j, '$') AS je, JSON_TYPE(j) AS tp FROM t ORDER BY je, id limit 2 offset 2 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select json_extract(`t`.`j`,'$') AS `je`,json_type(`t`.`j`) AS `tp` from `t` order by `je`,`t`.`id` limit 2,2" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`t`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t`", "table_scan": { "rows": 14, "cost": 0.518311 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 14, "access_type": "scan", "resulting_rows": 14, "cost": 1.91831, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 14, "cost_for_plan": 1.91831, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`je`,`t`.`id`", "items": [ { "item": "json_extract(`t`.`j`,'$')" }, { "item": "`t`.`id`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`je`,`t`.`id`" } } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`t`" } ] }, { "considering_tmp_tables": [ { "adding_sort_to_table": "t" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "t", "filesort_information": [ { "direction": "asc", "expression": "json_extract(`t`.`j`,'$')" }, { "direction": "asc", "expression": "`t`.`id`" } ], "filesort_priority_queue_optimization": { "limit": 4, "usable": false, "cause": "contains records of unbounded length" }, "filesort_execution": [ ], "filesort_summary": { "memory_available": 32768, "key_size": 4294967295, "row_size": 4294967295, "max_rows_per_buffer": 0, "num_rows_estimate": 15, "num_rows_found": 14, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": "NNN", "sort_algorithm": "std::sort", "sort_mode": "" } } ] } } ] } 0 0 DROP TABLE t; # --end of WL#8741 Varlen keys for sorting JSON values