Edit File: filesort_pq.result
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)); 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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true, "use_tmp_table": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "sort_cost": 100, "new_cost_for_plan": 122.49, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 100, "rows_estimate": 110, "row_size": 220, "memory_available": 262144, "chosen": false, "cause": "quicksort_is_cheaper" }, "filesort_execution": [ ], "filesort_summary": { "rows": 100, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 25080, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 110, "row_size": 220, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 7068, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": "Impossible WHERE" } } }, { "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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "desc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 110, "row_size": 416, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 13144, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": "Impossible WHERE" } } }, { "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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 20, "rows_estimate": 110, "row_size": 416, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 21, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 8904, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": [ { "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)" } ] } } ], "empty_result": { "cause": "Impossible WHERE" } } }, { "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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 20, "rows_estimate": 110, "row_size": 416, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 21, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 8904, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 10, "rows_estimate": 110, "row_size": 416, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 11, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 4664, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`foo`", "items": [ { "item": "concat('hello ',`t1`.`f2`)" } ], "resulting_clause_is_simple": true, "resulting_clause": "`foo`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "expression": "concat('hello ',`t1`.`f2`)" } ], "filesort_priority_queue_optimization": { "limit": 2, "rows_estimate": 110, "row_size": 410, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 3, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 1254, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "rand(2)", "items": [ { "item": "rand(2)" } ], "resulting_clause_is_simple": false, "resulting_clause": "rand(2)" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 219, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4788 } } }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "tmp_table_column" } ], "filesort_priority_queue_optimization": { "limit": 2, "rows_estimate": 110, "row_size": 16, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 3, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 72, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 110, "row_size": 220, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 7068, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": "Impossible WHERE" } } }, { "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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "desc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 110, "row_size": 416, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 13144, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": "Impossible WHERE" } } }, { "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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 20, "rows_estimate": 110, "row_size": 416, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 21, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 8904, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": [ { "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)" } ] } } ], "empty_result": { "cause": "Impossible WHERE" } } }, { "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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 20, "rows_estimate": 110, "row_size": 416, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 21, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 8904, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 10, "rows_estimate": 110, "row_size": 416, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 11, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 4664, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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 SELECT FOUND_ROWS(); FOUND_ROWS() 100 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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 110, "row_size": 220, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 7068, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY f1, f0 LIMIT 0; f0 f1 f2 SELECT FOUND_ROWS(); FOUND_ROWS() 100 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": [ { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 0, "rows_estimate": 110, "row_size": 220, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 1, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 228, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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 SELECT FOUND_ROWS(); FOUND_ROWS() 89 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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 20, "rows_estimate": 110, "row_size": 416, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 21, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 8904, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; f0 f1 f2 SELECT FOUND_ROWS(); FOUND_ROWS() 89 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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 0, "rows_estimate": 110, "row_size": 416, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 1, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 424, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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 SELECT FOUND_ROWS(); FOUND_ROWS() 89 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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 20, "rows_estimate": 110, "row_size": 416, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 21, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 8904, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 0 0 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; f0 f1 f2 SELECT FOUND_ROWS(); FOUND_ROWS() 89 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)" } ] } }, { "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": 2 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 22.488, "chosen": true } ] }, "cost_for_plan": 22.488, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 10, "rows_estimate": 110, "row_size": 416, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 11, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 4664, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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)); 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" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "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" } }, { "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`)" } ] } }, { "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": 4 } }, { "table": "`tmp`", "table_scan": { "rows": 300, "cost": 3 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 300, "cost": 63.465, "chosen": true } ] }, "cost_for_plan": 63.465, "rows_for_plan": 300, "rest_of_plan": [ { "plan_prefix": [ "`tmp`" ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 500, "cost": 30005, "chosen": true } ] }, "cost_for_plan": 30068, "rows_for_plan": 150000, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "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`)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`tmp`", "access_type": "table_scan" }, { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 235, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4462 } } }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "f1" }, { "direction": "asc", "table": "intermediate_tmp_table", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 1510, "row_size": 17, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 1500, "number_of_tmp_files": 0, "sort_buffer_size": 775, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "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" } }, { "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`)" } ] } }, { "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": 4 } }, { "table": "`tmp`", "table_scan": { "rows": 300, "cost": 3 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 300, "cost": 63.465, "chosen": true } ] }, "cost_for_plan": 63.465, "rows_for_plan": 300, "rest_of_plan": [ { "plan_prefix": [ "`tmp`" ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 500, "cost": 30005, "chosen": true } ] }, "cost_for_plan": 30068, "rows_for_plan": 150000, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "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`)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`tmp`", "access_type": "table_scan" }, { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 235, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4462 } } }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "f1" }, { "direction": "asc", "table": "intermediate_tmp_table", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 60, "rows_estimate": 1510, "row_size": 17, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 61, "examined_rows": 1500, "number_of_tmp_files": 0, "sort_buffer_size": 1525, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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 SELECT FOUND_ROWS(); FOUND_ROWS() 1500 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" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "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" } }, { "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`)" } ] } }, { "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": 4 } }, { "table": "`tmp`", "table_scan": { "rows": 300, "cost": 3 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 300, "cost": 63.465, "chosen": true } ] }, "cost_for_plan": 63.465, "rows_for_plan": 300, "rest_of_plan": [ { "plan_prefix": [ "`tmp`" ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 500, "cost": 30005, "chosen": true } ] }, "cost_for_plan": 30068, "rows_for_plan": 150000, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "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`)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`tmp`", "access_type": "table_scan" }, { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 235, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4462 } } }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "f1" }, { "direction": "asc", "table": "intermediate_tmp_table", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 60, "rows_estimate": 1510, "row_size": 17, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 61, "examined_rows": 1500, "number_of_tmp_files": 0, "sort_buffer_size": 1525, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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 SELECT FOUND_ROWS(); FOUND_ROWS() 1185 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" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "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" } }, { "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`))" } ] } }, { "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": 4 } }, { "table": "`tmp`", "table_scan": { "rows": 300, "cost": 3 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tmp`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 300, "cost": 63.465, "chosen": true } ] }, "cost_for_plan": 63.465, "rows_for_plan": 300, "rest_of_plan": [ { "plan_prefix": [ "`tmp`" ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 500, "cost": 30005, "chosen": true } ] }, "cost_for_plan": 30068, "rows_for_plan": 150000, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "pruned_by_heuristic": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`t1`.`f2` > 20) and (`t1`.`f2` = `tmp`.`f2`))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`tmp`", "attached": null }, { "table": "`t1`", "attached": "((`t1`.`f2` > 20) and (`t1`.`f2` = `tmp`.`f2`))" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`tmp`", "access_type": "table_scan" }, { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 235, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4462 } } }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "f1" }, { "direction": "asc", "table": "intermediate_tmp_table", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 60, "rows_estimate": 1195, "row_size": 17, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 61, "examined_rows": 1185, "number_of_tmp_files": 0, "sort_buffer_size": 1525, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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": [ { "view": { "database": "test", "view": "v1", "in_select#": 1, "select#": 2, "materialized": true } }, { "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" } ] } }, { "join_preparation": { "select#": 1, "steps": [ { "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": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "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": 11 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 30, "cost": 17.5, "chosen": true } ] }, "cost_for_plan": 17.5, "rows_for_plan": 30, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`v1`", "attached": null } ] } }, { "refine_plan": [ { "table": "`v1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "row_length": 210, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4993 } } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 510, "row_size": 220, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 7068, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } } ] } 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": [ { "view": { "database": "test", "view": "v1", "in_select#": 1, "select#": 2, "materialized": true } }, { "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" } ] } }, { "join_preparation": { "select#": 1, "steps": [ { "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": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "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": 15 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 35, "chosen": true } ] }, "cost_for_plan": 35, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`v1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`v1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "row_length": 210, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4993 } } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 100, "rows_estimate": 510, "row_size": 220, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 101, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 23028, "sort_mode": "<sort_key, additional_fields>" } } ] } }, { "filesort_information": [ { "direction": "asc", "table": "`v1`", "field": "f2" }, { "direction": "asc", "table": "`v1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 110, "row_size": 213, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 6851, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 0 0 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": [ { "view": { "database": "test", "view": "v1", "in_select#": 1, "select#": 2, "materialized": true } }, { "view": { "database": "test", "view": "v2", "in_select#": 1, "select#": 3, "materialized": true } }, { "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" } ] } }, { "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" } ] } }, { "join_preparation": { "select#": 1, "steps": [ { "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" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_optimization": { "select#": 3, "steps": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "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" } }, { "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`)" } ] } }, { "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": 15 } }, { "table": "`v2`", "table_scan": { "rows": 100, "cost": 15 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "<auto_key0>", "usable": false, "chosen": false }, { "access_type": "ref", "index": "<auto_key1>", "usable": false, "chosen": false }, { "access_type": "scan", "rows": 100, "cost": 35, "chosen": true } ] }, "cost_for_plan": 35, "rows_for_plan": 100, "rest_of_plan": [ { "plan_prefix": [ "`v1`" ], "table": "`v2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "<auto_key0>", "rows": 10, "cost": 1002, "chosen": true }, { "access_type": "ref", "index": "<auto_key1>", "rows": 10, "cost": 1002, "chosen": false }, { "access_type": "scan", "using_join_cache": true, "rows": 75, "cost": 1521.2, "chosen": false } ] }, "cost_for_plan": 1235, "rows_for_plan": 1000, "chosen": true } ] }, { "plan_prefix": [ ], "table": "`v2`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "<auto_key0>", "usable": false, "chosen": false }, { "access_type": "ref", "index": "<auto_key1>", "usable": false, "chosen": false }, { "access_type": "scan", "rows": 100, "cost": 35, "chosen": true } ] }, "cost_for_plan": 35, "rows_for_plan": 100, "rest_of_plan": [ { "plan_prefix": [ "`v2`" ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "<auto_key0>", "rows": 10, "cost": 1002, "chosen": true }, { "access_type": "ref", "index": "<auto_key1>", "rows": 10, "cost": 1002, "chosen": false }, { "access_type": "scan", "using_join_cache": true, "rows": 75, "cost": 1521.2, "chosen": false } ] }, "cost_for_plan": 1235, "rows_for_plan": 1000, "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": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`v1`", "access_type": "table_scan" }, { "table": "`v2`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "row_length": 210, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4993 } } }, { "creating_tmp_table": { "tmp_table_info": { "table": "`v2`", "row_length": 210, "key_length": 5, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4993 } } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 100, "rows_estimate": 510, "row_size": 220, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 101, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 23028, "sort_mode": "<sort_key, additional_fields>" } } ] } }, { "join_execution": { "select#": 3, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 100, "rows_estimate": 510, "row_size": 416, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 101, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 42824, "sort_mode": "<sort_key, additional_fields>" } } ] } }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 419, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 2502 } } }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "f2" }, { "direction": "asc", "table": "intermediate_tmp_table", "field": "f0" }, { "direction": "asc", "table": "intermediate_tmp_table", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 335, "row_size": 217, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 325, "number_of_tmp_files": 0, "sort_buffer_size": 6975, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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 1 order by 2,1 limit 5" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "2,1", "items": [ { "item": "count(`t1`.`f2`)" }, { "item": "floor((`t1`.`f1` / 10))" } ], "resulting_clause_is_simple": false, "resulting_clause": "2,1" } }, { "clause_processing": { "clause": "GROUP BY", "original_clause": "1", "items": [ { "item": "floor((`t1`.`f1` / 10))" } ], "resulting_clause_is_simple": true, "resulting_clause": "1" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 18, "key_length": 9, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 58254 } } }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "count(f2)" }, { "direction": "asc", "table": "intermediate_tmp_table", "field": "f3" } ], "filesort_priority_queue_optimization": { "limit": 5, "rows_estimate": 20, "row_size": 25, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 6, "examined_rows": 10, "number_of_tmp_files": 0, "sort_buffer_size": 198, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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 1 order by 2,1 limit 0" } ] } }, { "join_optimization": { "select#": 1, "steps": [ ], "empty_result": { "cause": "Impossible WHERE" } } }, { "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| 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)" } ] } }, { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 510, "row_size": 416, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 13144, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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)" } ] } }, { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 510, "row_size": 416, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 13144, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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)" } ] } }, { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` > 10)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` > 10)" } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 510, "row_size": 416, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 13144, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } 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": [ { "view": { "database": "test", "view": "v1", "in_select#": 1, "select#": 2, "materialized": true } }, { "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" } ] } }, { "join_preparation": { "select#": 1, "steps": [ { "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": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "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": 15 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`v1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 100, "cost": 35, "chosen": true } ] }, "cost_for_plan": 35, "rows_for_plan": 100, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`v1`", "attached": null } ] } }, { "clause_processing": { "clause": "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`" } }, { "refine_plan": [ { "table": "`v1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "`v1`", "row_length": 210, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4993 } } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" }, { "direction": "asc", "table": "`t1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 100, "rows_estimate": 510, "row_size": 220, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 101, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 23028, "sort_mode": "<sort_key, additional_fields>" } } ] } }, { "filesort_information": [ { "direction": "asc", "table": "`v1`", "field": "f2" }, { "direction": "asc", "table": "`v1`", "field": "f0" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 110, "row_size": 213, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 100, "number_of_tmp_files": 0, "sort_buffer_size": 6851, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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#": 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" } ] } }, { "join_preparation": { "select#": 1, "steps": [ { "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": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`t1`.`f1`", "items": [ { "item": "`t1`.`f1`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f1`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "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" } }, { "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": 4 } }, { "table": " `d1`", "table_scan": { "rows": 30, "cost": 11 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "rest_of_plan": [ { "plan_prefix": [ "`t1`" ], "table": " `d1`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "<auto_key0>", "rows": 3, "cost": 1500.6, "chosen": true }, { "access_type": "ref", "index": "<auto_key1>", "rows": 3, "cost": 1500.6, "chosen": false }, { "access_type": "scan", "using_join_cache": true, "rows": 23, "cost": 2313, "chosen": false } ] }, "cost_for_plan": 1904.4, "rows_for_plan": 1500, "sort_cost": 1500, "new_cost_for_plan": 3404.4, "chosen": true } ] } ] }, { "condition_on_constant_tables": "1", "condition_value": true }, { "attaching_conditions_to_tables": { "original_condition": "1", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null }, { "table": " `d1`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`d1`.`f2` desc", "items": [ { "item": "`d1`.`f2`" } ], "resulting_clause_is_simple": false, "resulting_clause": "`d1`.`f2` desc" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" }, { "table": " `d1`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": " `d1`", "row_length": 210, "key_length": 5, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 4993 } } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 510, "row_size": 216, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 6944, "sort_mode": "<sort_key, additional_fields>" } } ] } }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 206, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 5090 } } }, { "filesort_information": [ { "direction": "desc", "table": "intermediate_tmp_table", "field": "f2" } ], "filesort_priority_queue_optimization": { "limit": 30, "rows_estimate": 630, "row_size": 209, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 31, "examined_rows": 620, "number_of_tmp_files": 0, "sort_buffer_size": 6727, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 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 1 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 1 limit 1))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 1))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ], "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 1), `t1`.`f1`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 1), `t1`.`f1`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 1), `t1`.`f1`)" } ] } }, { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 1))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 1))" } ] } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "1", "items": [ { "item": "`t1`.`f1`" } ], "resulting_clause_is_simple": true, "resulting_clause": "1" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" } ], "filesort_priority_queue_optimization": { "limit": 1, "rows_estimate": 510, "row_size": 10, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 2, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 36, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_execution": { "select#": 2, "steps": [ ] } }, { "subselect_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 1 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 1 limit 2))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 2))", "steps": [ { "transformation": "equality_propagation", "subselect_evaluation": [ ], "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 2), `t1`.`f1`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 2), `t1`.`f1`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal((/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 2), `t1`.`f1`)" } ] } }, { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 2))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": "(`t1`.`f1` = (/* select#2 */ select `t1`.`f1` from `t1` order by 1 limit 2))" } ] } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "subselect_execution": { "select#": 2, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "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": 4 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 500, "cost": 104.44, "chosen": true } ] }, "cost_for_plan": 104.44, "rows_for_plan": 500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "1", "items": [ { "item": "`t1`.`f1`" } ], "resulting_clause_is_simple": true, "resulting_clause": "1" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 2, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f1" } ], "filesort_priority_queue_optimization": { "limit": 2, "rows_estimate": 510, "row_size": 10, "memory_available": 327680, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 3, "examined_rows": 500, "number_of_tmp_files": 0, "sort_buffer_size": 54, "sort_mode": "<sort_key, additional_fields>" } } ] } } ] } } ] } } ] } 0 0 CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); 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; INSERT INTO t1(f1,f2) SELECT * FROM tmp; 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; 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; INSERT INTO tmp SELECT f1,f2 FROM t1; INSERT INTO t1(f1,f2) SELECT * FROM tmp; set sort_buffer_size= 32768; SELECT * FROM t1 ORDER BY f2 LIMIT 100; f0 f1 f2 1 0 0 101 0 0 201 0 0 301 0 0 401 0 0 501 0 0 601 0 0 701 0 0 801 0 0 901 0 0 1001 0 0 1101 0 0 1201 0 0 1301 0 0 1401 0 0 1501 0 0 1601 0 0 1701 0 0 1801 0 0 1901 0 0 2001 0 0 2101 0 0 2201 0 0 2301 0 0 2401 0 0 2501 0 0 2601 0 0 2701 0 0 2801 0 0 2901 0 0 3001 0 0 3101 0 0 3201 0 0 3301 0 0 3401 0 0 3501 0 0 3601 0 0 3701 0 0 3801 0 0 3901 0 0 4001 0 0 4101 0 0 4201 0 0 4301 0 0 4401 0 0 4501 0 0 4601 0 0 4701 0 0 4801 0 0 4901 0 0 5001 0 0 5101 0 0 5201 0 0 5301 0 0 5401 0 0 5501 0 0 5601 0 0 5701 0 0 5801 0 0 5901 0 0 6001 0 0 6101 0 0 6201 0 0 6301 0 0 6401 0 0 6501 0 0 6601 0 0 6701 0 0 6801 0 0 6901 0 0 7001 0 0 7101 0 0 7201 0 0 7301 0 0 7401 0 0 7501 0 0 7601 0 0 7701 0 0 7801 0 0 7901 0 0 8001 0 0 8101 0 0 8201 0 0 8301 0 0 8401 0 0 8501 0 0 8601 0 0 8701 0 0 8801 0 0 8901 0 0 9001 0 0 9101 0 0 9201 0 0 9301 0 0 9401 0 0 9501 0 0 9601 0 0 9701 0 0 9801 0 0 9901 0 0 SELECT * FROM information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES SELECT * FROM t1 ORDER BY f2 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`.`f2` limit 100" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`t1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`t1`", "table_scan": { "rows": 438500, "cost": 2143 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 438500, "cost": 89843, "chosen": true } ] }, "cost_for_plan": 89843, "rows_for_plan": 438500, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`t1`", "attached": null } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`t1`.`f2`", "items": [ { "item": "`t1`.`f2`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`f2`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`t1`", "field": "f2" } ], "filesort_priority_queue_optimization": { "limit": 100, "rows_estimate": 438510, "row_size": 412, "memory_available": 32768, "strip_additional_fields": { "row_size": 215, "sort_merge_cost": 909261, "priority_queue_cost": 714335, "chosen": true } }, "filesort_execution": [ ], "filesort_summary": { "rows": 101, "examined_rows": 438500, "number_of_tmp_files": 0, "sort_buffer_size": 21715, "sort_mode": "<sort_key, rowid>" } } ] } } ] } 0 0 DROP TABLE t1, tmp; DROP VIEW v1, v2; # end of WL#1393 - Optimizing filesort with small limit # end of WL#5834 - Add optimizer traces for sorting