revision-id: a9af7ed477a46a0ee2cbb592a01167f08278f48b (mariadb-10.4.7-46-ga9af7ed477a) parent(s): fafa92684b418d846a5537ab15588f0ccaf1b6f1 author: Varun Gupta committer: Varun Gupta timestamp: 2019-09-02 00:21:56 +0530 message: MDEV-20444: More information regarding access of a table to be printed inside the optimizer_trace Added: 1) estimated_join_cardinality 2) best_chosen_access_method for a table 3) best_join_order --- mysql-test/main/opt_trace.result | 1528 ++++++++++++++++---- mysql-test/main/opt_trace_index_merge.result | 17 +- .../main/opt_trace_index_merge_innodb.result | 15 +- mysql-test/main/opt_trace_security.result | 34 +- sql/opt_trace.cc | 40 + sql/opt_trace.h | 3 + sql/sql_select.cc | 44 +- 7 files changed, 1385 insertions(+), 296 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index ab07db38d05..691a8370dc9 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -130,18 +130,29 @@ select * from v1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 1, "cost": 2.2044, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 1, + "cost": 2.2044, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 1, - "cost_for_plan": 2.4044 + "cost_for_plan": 2.4044, + "estimated_join_cardinality": 1 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1", @@ -265,18 +276,29 @@ select * from (select * from t1 where t1.a=1)q { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 1, "cost": 2.2044, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 1, + "cost": 2.2044, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 1, - "cost_for_plan": 2.4044 + "cost_for_plan": 2.4044, + "estimated_join_cardinality": 1 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1", @@ -405,19 +427,30 @@ select * from v2 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 1, "cost": 2.2044, "chosen": true, "use_tmp_table": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 1, + "cost": 2.2044, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 1, - "cost_for_plan": 2.4044 + "cost_for_plan": 2.4044, + "estimated_join_cardinality": 1 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1", @@ -462,18 +495,29 @@ select * from v2 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 2, "cost": 2, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 2, - "cost_for_plan": 2.4 + "cost_for_plan": 2.4, + "estimated_join_cardinality": 2 } ] }, + { + "best_join_order": ["<derived2>"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -591,18 +635,29 @@ explain select * from v2 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 10, "cost": 2.022, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.022, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 10, - "cost_for_plan": 4.022 + "cost_for_plan": 4.022, + "estimated_join_cardinality": 10 } ] }, + { + "best_join_order": ["t2"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -699,19 +754,30 @@ explain select * from v1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 10, "cost": 2.022, "chosen": true, "use_tmp_table": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.022, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 10, - "cost_for_plan": 4.022 + "cost_for_plan": 4.022, + "estimated_join_cardinality": 10 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -756,18 +822,29 @@ explain select * from v1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 10, "cost": 10, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 10, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 10, - "cost_for_plan": 12 + "cost_for_plan": 12, + "estimated_join_cardinality": 10 } ] }, + { + "best_join_order": ["<derived2>"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -917,12 +994,19 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 100, "cost": 2.3174, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 100, + "cost": 2.3174, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 100, "cost_for_plan": 22.317, @@ -942,15 +1026,23 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "chosen": true }, { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 100, "cost": 2.3174, "chosen": false } - ] + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 200, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 100, - "cost_for_plan": 242.32 + "cost_for_plan": 242.32, + "estimated_join_cardinality": 100 } ] }, @@ -960,12 +1052,19 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 100, "cost": 2.3174, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 100, + "cost": 2.3174, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 100, "cost_for_plan": 22.317, @@ -985,12 +1084,19 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "chosen": true }, { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 100, "cost": 2.3174, "chosen": false } - ] + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 200, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 100, "cost_for_plan": 242.32, @@ -1000,6 +1106,9 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { } ] }, + { + "best_join_order": ["t1", "t2"] + }, { "attaching_conditions_to_tables": { "original_condition": "t2.a = t1.b and t1.a = t2.b + 2", @@ -1146,18 +1255,29 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "range", + "access_type": "index_merge", "resulting_rows": 5, "cost": 6.75, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "index_merge", + "records": 5, + "cost": 6.75, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 5, - "cost_for_plan": 7.75 + "cost_for_plan": 7.75, + "estimated_join_cardinality": 5 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -1325,19 +1445,30 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 0.5849, "cost": 3.3121, "chosen": true, "use_tmp_table": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 0.5849, + "cost": 3.3121, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 0.5849, - "cost_for_plan": 3.4291 + "cost_for_plan": 3.4291, + "estimated_join_cardinality": 0.5849 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.b = 2 and t1.c = 3", @@ -1516,19 +1647,30 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 16, "cost": 2.0312, "chosen": true, "use_tmp_table": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 16, + "cost": 2.0312, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 16, - "cost_for_plan": 5.2313 + "cost_for_plan": 5.2313, + "estimated_join_cardinality": 16 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.a >= 20010104e0", @@ -1696,19 +1838,30 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 16, "cost": 2.0312, "chosen": true, "use_tmp_table": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 16, + "cost": 2.0312, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 16, - "cost_for_plan": 5.2313 + "cost_for_plan": 5.2313, + "estimated_join_cardinality": 16 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 20010104e0", @@ -1972,13 +2125,24 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "chosen": false, "cause": "cost" } - ] + ], + "chosen_access_method": { + "type": "ref", + "records": 21, + "cost": 22, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 21, - "cost_for_plan": 26.2 + "cost_for_plan": 26.2, + "estimated_join_cardinality": 21 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1 and t1.b = 2", @@ -2197,18 +2361,29 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 4, "cost": 2.0068, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.0068, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 4, - "cost_for_plan": 2.8068 + "cost_for_plan": 2.8068, + "estimated_join_cardinality": 4 } ] }, + { + "best_join_order": ["t2", "t1"] + }, { "condition_on_constant_tables": "1" }, @@ -2313,12 +2488,19 @@ explain select * from t1 left join t2 on t2.a=t1.a { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 4, "cost": 2.0068, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.0068, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 4, "cost_for_plan": 2.8068, @@ -2336,20 +2518,31 @@ explain select * from t1 left join t2 on t2.a=t1.a { "chosen": true }, { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 2, "cost": 8.0176, "chosen": false } - ] + ], + "chosen_access_method": { + "type": "eq_ref", + "records": 1, + "cost": 4, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 4, - "cost_for_plan": 7.6068 + "cost_for_plan": 7.6068, + "estimated_join_cardinality": 4 } ] } ] }, + { + "best_join_order": ["t1", "t2"] + }, { "condition_on_constant_tables": "1" }, @@ -2486,18 +2679,29 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 4, "cost": 2.0068, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.0068, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 4, - "cost_for_plan": 2.8068 + "cost_for_plan": 2.8068, + "estimated_join_cardinality": 4 } ] }, + { + "best_join_order": ["t3", "t2", "t1"] + }, { "condition_on_constant_tables": "1" }, @@ -2678,15 +2882,23 @@ explain extended select * from t1 where a in (select pk from t10) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 10, "cost": 2.022, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.022, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 10, - "cost_for_plan": 4.022 + "cost_for_plan": 4.022, + "estimated_join_cardinality": 10 } ] } @@ -2701,12 +2913,19 @@ explain extended select * from t1 where a in (select pk from t10) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0066, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0066, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6066, @@ -2718,12 +2937,19 @@ explain extended select * from t1 where a in (select pk from t10) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 10, "cost": 2.022, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.022, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 30, "cost_for_plan": 10.629, @@ -2746,7 +2972,8 @@ explain extended select * from t1 where a in (select pk from t10) { { "chosen_strategy": "SJ-Materialize" } - ] + ], + "estimated_join_cardinality": 3 } ] }, @@ -2756,12 +2983,19 @@ explain extended select * from t1 where a in (select pk from t10) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 10, "cost": 2.022, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 10, + "cost": 2.022, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 10, "cost_for_plan": 4.022, @@ -2782,6 +3016,9 @@ explain extended select * from t1 where a in (select pk from t10) { } ] }, + { + "best_join_order": ["t1", "<subquery2>"] + }, { "condition_on_constant_tables": "1" }, @@ -3105,13 +3342,24 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "chosen": false, "cause": "cost" } - ] + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 1.0043, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 1, - "cost_for_plan": 1.2043 + "cost_for_plan": 1.2043, + "estimated_join_cardinality": 1 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1", @@ -3214,18 +3462,29 @@ select f1(a) from t1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 4, "cost": 2.0068, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.0068, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 4, - "cost_for_plan": 2.8068 + "cost_for_plan": 2.8068, + "estimated_join_cardinality": 4 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -3302,18 +3561,29 @@ select f2(a) from t1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 4, "cost": 2.0068, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 4, + "cost": 2.0068, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 4, - "cost_for_plan": 2.8068 + "cost_for_plan": 2.8068, + "estimated_join_cardinality": 4 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -3354,7 +3624,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -1831 +2204 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3368,7 +3638,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 1731 0 + 2104 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3376,7 +3646,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 1831 0 +select * from t1 2204 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -3679,7 +3949,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "cost": 1.407, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "range", + "records": 3, + "cost": 1.407, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.007, @@ -3703,10 +3980,18 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "chosen": false, "cause": "cost" } - ] + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 3.007, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, - "cost_for_plan": 5.614 + "cost_for_plan": 5.614, + "estimated_join_cardinality": 3 } ] }, @@ -3721,7 +4006,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "cost": 1.407, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "range", + "records": 3, + "cost": 1.407, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.007, @@ -3745,7 +4037,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "chosen": false, "cause": "cost" } - ] + ], + "chosen_access_method": { + "type": "ref", + "records": 2, + "cost": 3.014, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 6, "cost_for_plan": 6.2211, @@ -3755,6 +4054,9 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { } ] }, + { + "best_join_order": ["t0", "t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = t0.a and t0.a < 3", @@ -3870,18 +4172,29 @@ explain select * from (select rand() from t1)q { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, - "cost_for_plan": 2.6051 + "cost_for_plan": 2.6051, + "estimated_join_cardinality": 3 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -3926,18 +4239,29 @@ explain select * from (select rand() from t1)q { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 3, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 3, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, - "cost_for_plan": 3.6 + "cost_for_plan": 3.6, + "estimated_join_cardinality": 3 } ] }, + { + "best_join_order": ["<derived2>"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -4124,12 +4448,19 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -4140,15 +4471,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, - "cost_for_plan": 6.4103 + "cost_for_plan": 6.4103, + "estimated_join_cardinality": 9 } ] }, @@ -4158,12 +4497,19 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -4182,12 +4528,19 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -4199,12 +4552,19 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 6.4103, @@ -4216,12 +4576,19 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 13.815, @@ -4244,7 +4611,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "chosen_strategy": "SJ-Materialize" } - ] + ], + "estimated_join_cardinality": 3 } ] }, @@ -4254,12 +4622,19 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 6.4103, @@ -4274,12 +4649,19 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -4292,12 +4674,19 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -4321,6 +4710,9 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ } ] }, + { + "best_join_order": ["t1", "<subquery2>"] + }, { "condition_on_constant_tables": "1" }, @@ -4591,12 +4983,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -4608,12 +5007,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 6.4103, @@ -4625,12 +5031,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 24.626, @@ -4656,12 +5069,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 34.174, @@ -4678,12 +5098,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 52.379, @@ -4701,12 +5128,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 729, "cost_for_plan": 200.19, @@ -4724,7 +5158,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "chosen_strategy": "FirstMatch" } - ] + ], + "estimated_join_cardinality": 27 } ] }, @@ -4739,12 +5174,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 243, "cost_for_plan": 84.79, @@ -4759,12 +5201,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 30.564, @@ -4781,12 +5230,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 48.779, @@ -4804,12 +5260,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 729, "cost_for_plan": 196.59, @@ -4838,12 +5301,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 48.779, @@ -4858,12 +5328,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 34.174, @@ -4878,12 +5355,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 24.626, @@ -4895,12 +5379,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 729, "cost_for_plan": 172.44, @@ -4922,12 +5413,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 243, "cost_for_plan": 75.231, @@ -4944,12 +5442,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 2187, "cost_for_plan": 514.65, @@ -4967,12 +5472,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 2187, "cost_for_plan": 514.65, @@ -4987,12 +5499,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 729, "cost_for_plan": 172.44, @@ -5007,12 +5526,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 13.815, @@ -5024,12 +5550,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 243, "cost_for_plan": 64.431, @@ -5046,12 +5579,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 2187, "cost_for_plan": 503.85, @@ -5069,12 +5609,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 2187, "cost_for_plan": 503.85, @@ -5089,12 +5636,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 243, "cost_for_plan": 64.431, @@ -5107,12 +5661,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 243, "cost_for_plan": 64.431, @@ -5127,12 +5688,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 24.626, @@ -5144,12 +5712,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 729, "cost_for_plan": 172.44, @@ -5166,12 +5741,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 2187, "cost_for_plan": 611.85, @@ -5189,12 +5771,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 6561, "cost_for_plan": 1486.7, @@ -5209,12 +5798,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 243, "cost_for_plan": 75.231, @@ -5231,12 +5827,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 2187, "cost_for_plan": 514.65, @@ -5254,12 +5857,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 2187, "cost_for_plan": 514.65, @@ -5274,12 +5884,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 729, "cost_for_plan": 172.44, @@ -5296,12 +5913,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 10.021, @@ -5314,12 +5938,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 10.021, @@ -5332,12 +5963,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 6.4103, @@ -5350,12 +5988,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 10.021, @@ -5370,12 +6015,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -5388,12 +6040,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 3.8154, @@ -5406,12 +6065,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 3.8154, @@ -5424,12 +6090,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -5442,12 +6115,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 3.8154, @@ -5467,12 +6147,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 162.42, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 162.42, + "uses_join_buffering": false, + "filter_used": false + } }, { "table": "t_inner_3" @@ -5480,12 +6167,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 489.74, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 489.74, + "uses_join_buffering": false, + "filter_used": false + } } ] }, @@ -5498,12 +6192,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 18.046, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 18.046, + "uses_join_buffering": false, + "filter_used": false + } }, { "table": "t_inner_2" @@ -5511,17 +6212,34 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 54.415, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 54.415, + "uses_join_buffering": false, + "filter_used": false + } } ] } ] }, + { + "best_join_order": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2", + "t_inner_4", + "t_inner_3" + ] + }, { "attaching_conditions_to_tables": { "original_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a", @@ -5796,12 +6514,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -5812,15 +6537,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, - "cost_for_plan": 10.021 + "cost_for_plan": 10.021, + "estimated_join_cardinality": 27 } ] }, @@ -5830,12 +6563,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 3.8154, @@ -5851,12 +6591,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -5867,15 +6614,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, - "cost_for_plan": 10.021 + "cost_for_plan": 10.021, + "estimated_join_cardinality": 27 } ] }, @@ -5885,12 +6640,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 3.8154, @@ -5909,12 +6671,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -5926,12 +6695,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 6.4103, @@ -5943,12 +6719,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 24.626, @@ -5979,12 +6762,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 15.541, @@ -6001,12 +6791,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 33.746, @@ -6024,12 +6821,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 729, "cost_for_plan": 181.56, @@ -6052,7 +6856,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "chosen_strategy": "SJ-Materialize" } - ] + ], + "estimated_join_cardinality": 27 } ] }, @@ -6067,12 +6872,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 243, "cost_for_plan": 66.156, @@ -6087,12 +6899,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 11.931, @@ -6109,12 +6928,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 30.146, @@ -6132,12 +6958,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 30.146, @@ -6152,12 +6985,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 15.541, @@ -6172,12 +7012,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 24.626, @@ -6190,12 +7037,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 13.815, @@ -6208,12 +7062,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 81, "cost_for_plan": 24.626, @@ -6228,12 +7089,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 10.021, @@ -6246,12 +7114,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 10.021, @@ -6264,12 +7139,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 6.4103, @@ -6282,12 +7164,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": true, + "filter_used": false + } }, "rows_for_plan": 27, "cost_for_plan": 10.021, @@ -6302,12 +7191,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -6320,12 +7216,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 3.8154, @@ -6338,12 +7241,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 3.8154, @@ -6356,12 +7266,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, "cost_for_plan": 2.6051, @@ -6374,12 +7291,19 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 9, "cost": 2.0154, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 9, + "cost": 2.0154, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 9, "cost_for_plan": 3.8154, @@ -6414,6 +7338,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } ] }, + { + "best_join_order": [ + "t_outer_1", + "<subquery2>", + "t_outer_2", + "<subquery3>" + ] + }, { "condition_on_constant_tables": "1" }, @@ -7052,12 +7984,20 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 5.9375, "cost": 2.8296, "chosen": true } - ] + ], + "chosen_access_method": + { + "type": "scan", + "records": 5.9375, + "cost": 2.8296, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 5.9375, "cost_for_plan": 4.0171, @@ -7076,15 +8016,24 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 804.69, "cost": 256.85, "chosen": true } - ] + ], + "chosen_access_method": + { + "type": "scan", + "records": 804.69, + "cost": 256.85, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 4777.8, - "cost_for_plan": 1216.4 + "cost_for_plan": 1216.4, + "estimated_join_cardinality": 4777.8 } ] }, @@ -7100,12 +8049,20 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 804.69, "cost": 43.26, "chosen": true } - ] + ], + "chosen_access_method": + { + "type": "scan", + "records": 804.69, + "cost": 43.26, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 804.69, "cost_for_plan": 204.2, @@ -7136,12 +8093,20 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 10, "cost": 2.0171, "chosen": true } - ] + ], + "chosen_access_method": + { + "type": "scan", + "records": 10, + "cost": 2.0171, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 10, "cost_for_plan": 4.0171, @@ -7170,16 +8135,25 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) }, { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 804.69, "cost": 43.26, "chosen": false } - ] + ], + "chosen_access_method": + { + "type": "ref", + "records": 1, + "cost": 20, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 10, "cost_for_plan": 26.017, - "selectivity": 0.8047 + "selectivity": 0.8047, + "estimated_join_cardinality": 8.0469 } ] }, @@ -7195,12 +8169,20 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 804.69, "cost": 43.26, "chosen": true } - ] + ], + "chosen_access_method": + { + "type": "scan", + "records": 804.69, + "cost": 43.26, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 804.69, "cost_for_plan": 204.2, diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index 5697e3a771a..7f1bd5163f3 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -207,18 +207,29 @@ explain select * from t1 where a=1 or b=1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "range", + "access_type": "index_merge", "resulting_rows": 2, "cost": 4.1484, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "index_merge", + "records": 2, + "cost": 4.1484, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 2, - "cost_for_plan": 4.5484 + "cost_for_plan": 4.5484, + "estimated_join_cardinality": 2 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.a = 1 or t1.b = 1", diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 23a500b0720..afcf0b03dae 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -208,13 +208,24 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "chosen": false, "cause": "cost" } - ] + ], + "chosen_access_method": { + "type": "ref", + "records": 1, + "cost": 2, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 1, - "cost_for_plan": 2.2 + "cost_for_plan": 2.2, + "estimated_join_cardinality": 1 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": "t1.key1 = 1 and t1.pk1 <> 0", diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index c8112fd5f6c..2b9ecfd46a3 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -93,18 +93,29 @@ select * from db1.t1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, - "cost_for_plan": 2.6051 + "cost_for_plan": 2.6051, + "estimated_join_cardinality": 3 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": null, @@ -206,18 +217,29 @@ select * from db1.v1 { "best_access_path": { "considered_access_paths": [ { - "access_type": "scan", + "access_type": "ALL", "resulting_rows": 3, "cost": 2.0051, "chosen": true } - ] + ], + "chosen_access_method": { + "type": "scan", + "records": 3, + "cost": 2.0051, + "uses_join_buffering": false, + "filter_used": false + } }, "rows_for_plan": 3, - "cost_for_plan": 2.6051 + "cost_for_plan": 2.6051, + "estimated_join_cardinality": 3 } ] }, + { + "best_join_order": ["t1"] + }, { "attaching_conditions_to_tables": { "original_condition": null, diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index befc7934a3a..60855729eea 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -630,6 +630,46 @@ void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab) table_rec.add("rows", tab->found_records) .add("cost", tab->read_time); } + +/* + Print the join order of all the tables for a select. + + For example: + + select * from ot1 + where ot1.a IN (select it1.a from it1, it2 where it1.b=it2.a); + + So this function would print + ot1, <subquery2> ----> For select #1 + it1,it2 ----> For select #2 +*/ + +void print_final_join_order(JOIN *join) +{ + Json_writer_object join_order(join->thd); + Json_writer_array best_order(join->thd, "best_join_order"); + JOIN_TAB *j; + uint i; + for (j= join->join_tab,i=0 ; i < join->top_join_tab_count; + i++, j++) + best_order.add_table_name(j); +} + + +void print_best_access_for_table(THD *thd, POSITION *pos, + enum join_type type) +{ + Json_writer_object trace_best_access(thd, "chosen_access_method"); + trace_best_access.add("type", type == JT_ALL ? "scan" : + join_type_str[type]); + trace_best_access.add("records", pos->records_read); + trace_best_access.add("cost", pos->read_time); + trace_best_access.add("uses_join_buffering", pos->use_join_buffer); + trace_best_access.add("filter_used", + pos->range_rowid_filter_info != NULL); +} + + /* Introduce enum_query_type flags parameter, maybe also allow EXPLAIN also use this function. diff --git a/sql/opt_trace.h b/sql/opt_trace.h index 52318bc6b7f..6fe179d79d8 100644 --- a/sql/opt_trace.h +++ b/sql/opt_trace.h @@ -105,6 +105,9 @@ void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex, Json_writer_object *trace_object); void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab); +void print_final_join_order(JOIN *join); +void print_best_access_for_table(THD *thd, POSITION *pos, + enum join_type type); /* Security related (need to add a proper comment here) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 150e8008096..8e95490e8ce 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7203,6 +7203,7 @@ best_access_path(JOIN *join, SplM_plan_info *spl_plan= 0; Range_rowid_filter_cost_info *filter= 0; const char* cause= NULL; + enum join_type best_type= JT_UNKNOWN, type= JT_UNKNOWN; disable_jbuf= disable_jbuf || idx == join->const_tables; @@ -7342,7 +7343,8 @@ best_access_path(JOIN *join, */ tmp= prev_record_reads(join->positions, idx, found_ref); records= 1.0; - trace_access_idx.add("access_type", "fulltext") + type= JT_FT; + trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); } else @@ -7365,14 +7367,16 @@ best_access_path(JOIN *join, (!(key_flags & HA_NULL_PART_KEY) || // (2) all_key_parts == notnull_part)) // (3) { - trace_access_idx.add("access_type", "eq_ref") + type= JT_EQ_REF; + trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); tmp = prev_record_reads(join->positions, idx, found_ref); records=1.0; } else { - trace_access_idx.add("access_type", "ref") + type= JT_REF; + trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); if (!found_ref) { /* We found a const key */ @@ -7467,8 +7471,8 @@ best_access_path(JOIN *join, } else { - trace_access_idx.add("access_type", - ref_or_null_part ? "ref_or_null" : "ref") + type = ref_or_null_part ? JT_REF_OR_NULL : JT_REF; + trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); /* Use as much key-parts as possible and a uniq key is better @@ -7683,6 +7687,7 @@ best_access_path(JOIN *join, best_max_key_part= max_key_part; best_ref_depends_map= found_ref; best_filter= filter; + best_type= type; } else { @@ -7736,6 +7741,7 @@ best_access_path(JOIN *join, best_ref_depends_map= 0; best_uses_jbuf= TRUE; best_filter= 0; + best_type= JT_HASH; trace_access_hash.add("type", "hash"); trace_access_hash.add("index", "hj-key"); trace_access_hash.add("cost", rnd_records); @@ -7799,10 +7805,6 @@ best_access_path(JOIN *join, filter= 0; if (s->quick) { - trace_access_scan.add("access_type", "range"); - /* - should have some info about all the different QUICK_SELECT - */ /* For each record we: - read record range through 'quick' @@ -7828,23 +7830,29 @@ best_access_path(JOIN *join, { tmp-= filter->get_adjusted_gain(rows); DBUG_ASSERT(tmp >= 0); - } + } + type= JT_RANGE; } else { + type= JT_INDEX_MERGE; best_filter= 0; } - loose_scan_opt.check_range_access(join, idx, s->quick); } else { - trace_access_scan.add("access_type", "scan"); /* Estimate cost of reading table. */ if (s->table->force_index && !best_key) // index scan + { + type= JT_NEXT; tmp= s->table->file->read_time(s->ref.key, 1, s->records); + } else // table scan + { tmp= s->scan_time(); + type= JT_ALL; + } if ((s->table->map & join->outer_join) || disable_jbuf) // Can't use join cache { @@ -7874,6 +7882,7 @@ best_access_path(JOIN *join, } } + trace_access_scan.add("access_type", join_type_str[type]); /* Splitting technique cannot be used with join cache */ if (s->table->is_splittable()) tmp+= s->table->get_materialization_cost(); @@ -7913,6 +7922,7 @@ best_access_path(JOIN *join, best_uses_jbuf= MY_TEST(!disable_jbuf && !((s->table->map & join->outer_join))); spl_plan= 0; + best_type= type; } trace_access_scan.add("chosen", best_key == NULL); } @@ -7944,6 +7954,11 @@ best_access_path(JOIN *join, trace_access_scan.add("use_tmp_table", true); join->sort_by_table= (TABLE*) 1; // Must use temporary table } + trace_access_scan.end(); + trace_paths.end(); + + if (unlikely(thd->trace_started())) + print_best_access_for_table(thd, pos, best_type); DBUG_VOID_RETURN; } @@ -9483,6 +9498,8 @@ best_extension_by_limited_search(JOIN *join, Hence it may be wrong. */ current_read_time= COST_ADD(current_read_time, current_record_count); + trace_one_table.add("estimated_join_cardinality", + partial_join_cardinality); if (current_read_time < join->best_read) { memcpy((uchar*) join->best_positions, (uchar*) join->positions, @@ -10304,6 +10321,9 @@ bool JOIN::get_best_combination() top_join_tab_count= (uint)(join_tab_ranges.head()->end - join_tab_ranges.head()->start); + if (unlikely(thd->trace_started())) + print_final_join_order(this); + update_depend_map(this); DBUG_RETURN(0); }