revision-id: 5ae3aec10a31f969b0095ba2c0b83e035880eaf3 (mariadb-10.3.6-123-g5ae3aec10a3) parent(s): 790b6f5ae2b82f5e2d9c872c52b71b6f5fe0c35a author: Varun Gupta committer: Varun Gupta timestamp: 2019-02-18 03:49:11 +0530 message: Minor cleanup in the optimizer trace code. More test coverage added for the optimizer trace. --- mysql-test/main/opt_trace.result | 2903 ++++++++++++++++++-- mysql-test/main/opt_trace.test | 41 + mysql-test/main/opt_trace_index_merge.result | 10 +- .../main/opt_trace_index_merge_innodb.result | 10 +- mysql-test/main/opt_trace_security.result | 18 +- sql/my_json_writer.cc | 20 +- sql/my_json_writer.h | 20 +- sql/opt_range.cc | 105 +- sql/opt_subselect.cc | 77 +- sql/opt_table_elimination.cc | 23 +- sql/opt_trace.cc | 160 +- sql/opt_trace.h | 15 +- sql/opt_trace_context.h | 15 +- sql/sql_class.cc | 2 +- sql/sql_derived.cc | 43 +- sql/sql_select.cc | 44 +- sql/sql_test.cc | 10 +- 17 files changed, 3042 insertions(+), 474 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 57ed163c68b..fbe8743e080 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -45,7 +45,7 @@ select * from v1 { "view": { "table": "v1", "select_id": 2, - "merged": true + "algorithm": "merged" } }, { @@ -53,13 +53,13 @@ select * from v1 { "select_id": 2, "steps": [ { - "expanded_query": "/* select#2 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` = 1" + "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" } ] } }, { - "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `v1`" + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from v1" } ] } @@ -108,9 +108,10 @@ select * from v1 { "selectivity_for_columns": [ { "column_name": "a", - "selectivity_from_histograms": 0.5 + "selectivity_from_histogram": 0.5 } - ] + ], + "cond_selectivity": 0.5 }, { "table": "t1", @@ -121,11 +122,6 @@ select * from v1 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -182,7 +178,7 @@ select * from (select * from t1 where t1.a=1)q { "derived": { "table": "q", "select_id": 2, - "merged": true + "algorithm": "merged" } }, { @@ -190,13 +186,13 @@ select * from (select * from t1 where t1.a=1)q { "select_id": 2, "steps": [ { - "expanded_query": "/* select#2 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` = 1" + "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1" } ] } }, { - "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from (/* select#2 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` = 1) `q`" + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q" } ] } @@ -245,9 +241,10 @@ select * from (select * from t1 where t1.a=1)q { "selectivity_for_columns": [ { "column_name": "a", - "selectivity_from_histograms": 0.5 + "selectivity_from_histogram": 0.5 } - ] + ], + "cond_selectivity": 0.5 }, { "table": "t1", @@ -258,11 +255,6 @@ select * from (select * from t1 where t1.a=1)q { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -320,7 +312,7 @@ select * from v2 { "view": { "table": "v2", "select_id": 2, - "materialized": true + "algorithm": "materialized" } }, { @@ -328,13 +320,13 @@ select * from v2 { "select_id": 2, "steps": [ { - "expanded_query": "/* select#2 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` = 1 group by `t1`.`b`" + "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1 group by t1.b" } ] } }, { - "expanded_query": "/* select#1 */ select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `v2`" + "expanded_query": "/* select#1 */ select v2.a AS a,v2.b AS b from v2" } ] } @@ -387,9 +379,10 @@ select * from v2 { "selectivity_for_columns": [ { "column_name": "a", - "selectivity_from_histograms": 0.5 + "selectivity_from_histogram": 0.5 } - ] + ], + "cond_selectivity": 0.5 }, { "table": "t1", @@ -400,11 +393,6 @@ select * from v2 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -460,11 +448,6 @@ select * from v2 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -548,7 +531,7 @@ explain select * from v2 { "view": { "table": "v2", "select_id": 2, - "merged": true + "algorithm": "merged" } }, { @@ -556,13 +539,13 @@ explain select * from v2 { "select_id": 2, "steps": [ { - "expanded_query": "/* select#2 */ select `t2`.`a` AS `a` from `t2`" + "expanded_query": "/* select#2 */ select t2.a AS a from t2" } ] } }, { - "expanded_query": "/* select#1 */ select `t2`.`a` AS `a` from `v2`" + "expanded_query": "/* select#1 */ select t2.a AS a from v2" } ] } @@ -592,11 +575,6 @@ explain select * from v2 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -655,7 +633,7 @@ explain select * from v1 { "view": { "table": "v1", "select_id": 2, - "materialized": true + "algorithm": "materialized" } }, { @@ -663,13 +641,13 @@ explain select * from v1 { "select_id": 2, "steps": [ { - "expanded_query": "/* select#2 */ select `t1`.`a` AS `a` from `t1` group by `t1`.`b`" + "expanded_query": "/* select#2 */ select t1.a AS a from t1 group by t1.b" } ] } }, { - "expanded_query": "/* select#1 */ select `v1`.`a` AS `a` from `v1`" + "expanded_query": "/* select#1 */ select v1.a AS a from v1" } ] } @@ -703,11 +681,6 @@ explain select * from v1 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -763,11 +736,6 @@ explain select * from v1 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -848,7 +816,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t2`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`c` AS `c` from `t1` join `t2` where `t1`.`a` = `t2`.`b` + 2 and `t2`.`a` = `t1`.`b`" + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t2.a AS a,t2.b AS b,t2.c AS c from t1 join t2 where t1.a = t2.b + 2 and t2.a = t1.b" } ] } @@ -927,11 +895,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -1063,7 +1026,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "select_id": 1, "steps": [ { - "expanded_query": "select distinct `t1`.`a` AS `a` from `t1`" + "expanded_query": "select distinct t1.a AS a from t1" } ] } @@ -1122,7 +1085,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "best_group_range_summary": { "type": "index_group", "index": "a", - "group_attribute": null, + "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, @@ -1136,7 +1099,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "range_access_plan": { "type": "index_group", "index": "a", - "group_attribute": null, + "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, @@ -1153,11 +1116,6 @@ EXPLAIN SELECT DISTINCT a FROM t1 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -1221,7 +1179,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "select_id": 1, "steps": [ { - "expanded_query": "select min(`t1`.`d`) AS `MIN(d)` from `t1` where `t1`.`b` = 2 and `t1`.`c` = 3 group by `t1`.`a`" + "expanded_query": "select min(t1.d) AS `MIN(d)` from t1 where t1.b = 2 and t1.c = 3 group by t1.a" } ] } @@ -1299,7 +1257,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "best_group_range_summary": { "type": "index_group", "index": "a", - "group_attribute": "d", + "min_max_arg": "d", "min_aggregate": true, "max_aggregate": false, "distinct_aggregate": false, @@ -1324,21 +1282,17 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "selectivity_for_columns": [ { "column_name": "b", - "selectivity_from_histograms": 0.1667 + "selectivity_from_histogram": 0.1667 }, { "column_name": "c", - "selectivity_from_histograms": 0.25 + "selectivity_from_histogram": 0.25 } - ] + ], + "cond_selectivity": 0.0417 } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -1423,7 +1377,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`id` AS `id`,min(`t1`.`a`) AS `MIN(a)`,max(`t1`.`a`) AS `MAX(a)` from `t1` where `t1`.`a` >= 20010104e0 group by `t1`.`id`" + "expanded_query": "select t1.`id` AS `id`,min(t1.a) AS `MIN(a)`,max(t1.a) AS `MAX(a)` from t1 where t1.a >= 20010104e0 group by t1.`id`" } ] } @@ -1501,7 +1455,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "best_group_range_summary": { "type": "index_group", "index": "id", - "group_attribute": "a", + "min_max_arg": "a", "min_aggregate": true, "max_aggregate": true, "distinct_aggregate": false, @@ -1523,15 +1477,11 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { }, { "selectivity_for_indexes": [], - "selectivity_for_columns": [] + "selectivity_for_columns": [], + "cond_selectivity": 1 } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -1605,7 +1555,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`id` AS `id`,`t1`.`a` AS `a` from `t1` where `t1`.`a` = 20010104e0 group by `t1`.`id`" + "expanded_query": "select t1.`id` AS `id`,t1.a AS a from t1 where t1.a = 20010104e0 group by t1.`id`" } ] } @@ -1683,7 +1633,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "best_group_range_summary": { "type": "index_group", "index": "id", - "group_attribute": null, + "min_max_arg": null, "min_aggregate": false, "max_aggregate": false, "distinct_aggregate": false, @@ -1705,15 +1655,11 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { }, { "selectivity_for_indexes": [], - "selectivity_for_columns": [] + "selectivity_for_columns": [], + "cond_selectivity": 1 } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -1814,7 +1760,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`pk` AS `pk`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t1`.`filler` AS `filler` from `t1` where `t1`.`a` = 1 and `t1`.`b` = 2 order by `t1`.`c` limit 1" + "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 and t1.b = 2 order by t1.c limit 1" } ] } @@ -1957,21 +1903,17 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "selectivity_for_columns": [ { "column_name": "a", - "selectivity_from_histograms": 0.0012 + "selectivity_from_histogram": 0.0012 }, { "column_name": "b", - "selectivity_from_histograms": 0.001 + "selectivity_from_histogram": 0.001 } - ] + ], + "cond_selectivity": 0.021 } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -2160,7 +2102,7 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`a` AS `a` from (`t1` left join `t2` on(`t1`.`a` = `t2`.`a`))" + "expanded_query": "select t1.a AS a from (t1 left join t2 on(t1.a = t2.a))" } ] } @@ -2215,11 +2157,6 @@ select t1.a from t1 left join t2 on t1.a=t2.a { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -2278,7 +2215,7 @@ explain select * from t1 left join t2 on t2.a=t1.a { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`a` AS `a`,`t2`.`a` AS `a`,`t2`.`b` AS `b` from (`t1` left join `t2` on(`t2`.`a` = `t1`.`a`))" + "expanded_query": "select t1.a AS a,t2.a AS a,t2.b AS b from (t1 left join t2 on(t2.a = t1.a))" } ] } @@ -2334,11 +2271,6 @@ explain select * from t1 left join t2 on t2.a=t1.a { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -2423,7 +2355,7 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`a` AS `a` from (`t1` left join (`t2` join `t3` on(`t2`.`b` = `t3`.`b`)) on(`t2`.`a` = `t1`.`a` and `t3`.`a` = `t1`.`a`))" + "expanded_query": "select t1.a AS a from (t1 left join (t2 join t3 on(t2.b = t3.b)) on(t2.a = t1.a and t3.a = t1.a))" } ] } @@ -2508,11 +2440,6 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -2615,13 +2542,13 @@ explain extended select * from t1 where a in (select pk from t10) { } }, { - "expanded_query": "/* select#2 */ select `t10`.`pk` from `t10`" + "expanded_query": "/* select#2 */ select t10.pk from t10" } ] } }, { - "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`a` in (/* select#2 */ select `t10`.`pk` from `t10`)" + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)" } ] } @@ -2768,6 +2695,18 @@ explain extended select * from t1 where a in (select pk from t10) { } ] }, + { + "fix_semijoin_strategies_for_picked_join_order": [ + { + "semi_join_strategy": "sj_materialize", + "join_order": [ + { + "table": "t10" + } + ] + } + ] + }, { "condition_on_constant_tables": "1" }, @@ -2837,7 +2776,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`pk` AS `pk`,`t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where `t1`.`pk` = 2 and `t1`.`a` = 5 and `t1`.`b` = 1" + "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b from t1 where t1.pk = 2 and t1.a = 5 and t1.b = 1" } ] } @@ -3043,21 +2982,17 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "selectivity_for_columns": [ { "column_name": "a", - "selectivity_from_histograms": 0.1 + "selectivity_from_histogram": 0.1 }, { "column_name": "b", - "selectivity_from_histograms": 0.1 + "selectivity_from_histogram": 0.1 } - ] + ], + "cond_selectivity": 0.1 } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -3164,7 +3099,7 @@ select f1(a) from t1 { "select_id": 1, "steps": [ { - "expanded_query": "select `f1`(`t1`.`a`) AS `f1(a)` from `t1`" + "expanded_query": "select f1(t1.a) AS `f1(a)` from t1" } ] } @@ -3194,11 +3129,6 @@ select f1(a) from t1 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -3255,7 +3185,7 @@ select f2(a) from t1 { "select_id": 1, "steps": [ { - "expanded_query": "select `f2`(`t1`.`a`) AS `f2(a)` from `t1`" + "expanded_query": "select f2(t1.a) AS `f2(a)` from t1" } ] } @@ -3285,11 +3215,6 @@ select f2(a) from t1 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -3348,7 +3273,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -1889 +1754 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3362,7 +3287,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 1789 0 + 1654 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3370,7 +3295,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 1889 0 +select * from t1 1754 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -3444,6 +3369,7 @@ set optimizer_trace='enabled=off'; # # MDEV-18528: Optimizer trace support for multi-table UPDATE and DELETE # +set optimizer_trace=1; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t0 (a int, b int); @@ -3457,55 +3383,2642 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 5 test.t0.a 1 select * from information_schema.optimizer_trace; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -explain delete from t0 where t0.a<3 { +explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "steps": [ { - "table": "t0", - "range_analysis": { - "table_scan": { - "rows": 10, - "cost": 6.122 - }, - "potential_range_indexes": [ + "join_preparation": { + "select_id": 1, + "steps": [ { - "index": "a", - "usable": true, - "key_parts": ["a"] + "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3" } - ], - "setup_range_conditions": [], - "group_index_range": { - "chosen": false, - "cause": "no join" - }, - "analyzing_range_alternatives": { - "range_scan_alternatives": [ - { - "index": "a", - "ranges": ["NULL < a < 3"], - "rowid_ordered": false, - "using_mrr": false, - "index_only": false, - "rows": 3, - "cost": 5.007, - "chosen": true + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t0.a = t1.a and t1.a < 3", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)" + } + ] } - ], - "analyzing_index_merge_union": [] - }, - "chosen_range_access_summary": { - "range_access_plan": { - "type": "range_scan", - "index": "a", - "rows": 3, - "ranges": ["NULL < a < 3"] }, - "rows_for_plan": 3, - "cost_for_plan": 5.007, - "chosen": true - } - } - } - ] -} 0 0 -drop table ten,t0,t1; + { + "table_dependencies": [ + { + "table": "t0", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t0", + "field": "a", + "equals": "t1.a", + "null_rejecting": true + }, + { + "table": "t1", + "field": "a", + "equals": "t0.a", + "null_rejecting": true + } + ] + }, + { + "rows_estimation": [ + { + "table": "t0", + "range_analysis": { + "table_scan": { + "rows": 10, + "cost": 6.122 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + } + ], + "best_covering_index_scan": { + "index": "a", + "cost": 1.5234, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "not single_table" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["NULL < a < 3"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": true, + "rows": 3, + "cost": 1.407, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 3, + "ranges": ["NULL < a < 3"] + }, + "rows_for_plan": 3, + "cost_for_plan": 1.407, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a", + "selectivity_from_index": 0.3 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 0.3 + }, + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 10, + "cost": 6.122 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + } + ], + "best_covering_index_scan": { + "index": "a", + "cost": 1.5234, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "not single_table" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["NULL < a < 3"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": true, + "rows": 3, + "cost": 1.407, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 3, + "ranges": ["NULL < a < 3"] + }, + "rows_for_plan": 3, + "cost_for_plan": 1.407, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a", + "selectivity_from_index": 0.3 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 0.3 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t0", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "range", + "resulting_rows": 3, + "cost": 1.407, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t0"], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "cause": "not better than ref estimates", + "rows": 1, + "cost": 3.007, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "range", + "resulting_rows": 3, + "cost": 1.407, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t1"], + "table": "t0", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "cause": "not better than ref estimates", + "rows": 2, + "cost": 3.014, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + }, + "pruned_by_cost": true + } + ] + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = t0.a and t0.a < 3", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t0", + "attached": "t0.a < 3 and t0.a is not null" + }, + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +drop table ten,t0,t1; +set optimizer_trace='enabled=off'; +# +# Merged to Materialized for derived tables +# +set optimizer_trace=1; +create table t1 (a int); +insert into t1 values (1),(2),(3); +explain select * from (select rand() from t1)q; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain select * from (select rand() from t1)q { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "derived": { + "table": "q", + "select_id": 2, + "algorithm": "merged" + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select rand() AS `rand()` from t1" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select rand() AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "derived": { + "table": "q", + "select_id": 2, + "algorithm": "materialized", + "cause": "Random function in the select" + } + }, + { + "join_optimization": { + "select_id": 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": 3, + "cost": 2.0051 + } + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "<derived2>", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "<derived2>", + "table_scan": { + "rows": 3, + "cost": 3 + } + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "<derived2>", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 3, + "chosen": true + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "<derived2>", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [ + { + "join_execution": { + "select_id": 2, + "steps": [] + } + } + ] + } + } + ] +} 0 0 +drop table t1; +set optimizer_trace='enabled=off'; +# +# Semi-join nest +# +set optimizer_trace=1; +create table t1 (a int); +insert into t1 values (1),(2),(3); +create table t2(a int); +insert into t2 values (1),(2),(3),(1),(2),(3),(1),(2),(3); +set @save_optimizer_switch= @@optimizer_switch; +explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2) { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "chosen": true + } + }, + { + "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select t1.a AS a from t1 where t1.a in (/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2)" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "converted_to_semi_join": true + } + }, + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "1 and t1.a = t_inner_1.a", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "1 and multiple equal(t1.a, t_inner_1.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "1 and multiple equal(t1.a, t_inner_1.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(t1.a, t_inner_1.a)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_1", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_2", + "row_may_be_null": false, + "map_bit": 2, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + }, + { + "table": "t_inner_1", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + }, + { + "table": "t_inner_2", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [ + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_inner_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + } + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + } + ] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t1"], + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t1", "t_inner_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + } + } + ] + }, + { + "plan_prefix": ["t1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "fix_semijoin_strategies_for_picked_join_order": [ + { + "semi_join_strategy": "sj_materialize", + "join_order": [ + { + "table": "t_inner_1" + }, + { + "table": "t_inner_2" + } + ] + } + ] + }, + { + "condition_on_constant_tables": "1" + }, + { + "attaching_conditions_to_tables": { + "original_condition": "1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + }, + { + "table": "t_inner_1", + "attached": null + }, + { + "table": "t_inner_2", + "attached": null + }, + { + "table": "<subquery2>", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +# with Firstmatch, mostly for tracing fix_semijoin_strategies_for_picked_join_order +set optimizer_switch='materialization=off'; +explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and +t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 +1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 FirstMatch(t_outer_1); Using join buffer (incremental, BNL join) +1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; FirstMatch(t_outer_2); Using join buffer (incremental, BNL join) +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and +t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "chosen": true + } + }, + { + "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" + } + ] + } + }, + { + "join_preparation": { + "select_id": 3, + "steps": [ + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "semijoin", + "chosen": true + } + }, + { + "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "converted_to_semi_join": true + } + }, + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "semijoin", + "converted_to_semi_join": true + } + }, + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "1 and 1 and t_outer_1.a = t_inner_1.a and t_outer_2.a = t_inner_3.a", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t_outer_1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t_outer_2", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_2", + "row_may_be_null": false, + "map_bit": 2, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_1", + "row_may_be_null": false, + "map_bit": 3, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_3", + "row_may_be_null": false, + "map_bit": 4, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_4", + "row_may_be_null": false, + "map_bit": 5, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t_outer_1", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + }, + { + "table": "t_outer_2", + "table_scan": { + "rows": 9, + "cost": 2.0154 + } + }, + { + "table": "t_inner_2", + "table_scan": { + "rows": 9, + "cost": 2.0154 + } + }, + { + "table": "t_inner_1", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + }, + { + "table": "t_inner_3", + "table_scan": { + "rows": 9, + "cost": 2.0154 + } + }, + { + "table": "t_inner_4", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t_outer_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + } + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4", + "t_outer_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_4", + "t_outer_2" + ], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_4", + "t_outer_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_3", + "t_outer_2" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_3", + "t_outer_2" + ], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_3", + "t_inner_4" + ], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_3", + "t_inner_4" + ], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + } + ] + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "fix_semijoin_strategies_for_picked_join_order": [ + { + "semi_join_strategy": "firstmatch", + "join_order": [ + { + "table": "t_inner_4" + }, + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 162.42, + "chosen": true + } + ] + }, + { + "table": "t_inner_3" + }, + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 489.74, + "chosen": true + } + ] + } + ] + }, + { + "semi_join_strategy": "firstmatch", + "join_order": [ + { + "table": "t_inner_1" + }, + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 18.046, + "chosen": true + } + ] + }, + { + "table": "t_inner_2" + }, + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 54.415, + "chosen": true + } + ] + } + ] + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t_outer_1", + "attached": null + }, + { + "table": "t_inner_1", + "attached": "t_inner_1.a = t_outer_1.a" + }, + { + "table": "t_inner_2", + "attached": null + }, + { + "table": "t_outer_2", + "attached": null + }, + { + "table": "t_inner_4", + "attached": null + }, + { + "table": "t_inner_3", + "attached": "t_inner_3.a = t_outer_2.a" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +set optimizer_switch='materialization=on'; +explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and +t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t_inner_1 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t_inner_2 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) +3 MATERIALIZED t_inner_4 ALL NULL NULL NULL NULL 3 +3 MATERIALIZED t_inner_3 ALL NULL NULL NULL NULL 9 Using join buffer (flat, BNL join) +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and +t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "chosen": true + } + }, + { + "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" + } + ] + } + }, + { + "join_preparation": { + "select_id": 3, + "steps": [ + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "materialization", + "sjm_scan_allowed": true, + "possible": true + } + }, + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "semijoin", + "chosen": true + } + }, + { + "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" + } + ] + } + }, + { + "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "transformation": { + "select_id": 2, + "from": "IN (SELECT)", + "to": "semijoin", + "converted_to_semi_join": true + } + }, + { + "transformation": { + "select_id": 3, + "from": "IN (SELECT)", + "to": "semijoin", + "converted_to_semi_join": true + } + }, + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "1 and 1 and t_outer_1.a = t_inner_1.a and t_outer_2.a = t_inner_3.a", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t_outer_1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t_outer_2", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_2", + "row_may_be_null": false, + "map_bit": 2, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_1", + "row_may_be_null": false, + "map_bit": 3, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_3", + "row_may_be_null": false, + "map_bit": 4, + "depends_on_map_bits": [] + }, + { + "table": "t_inner_4", + "row_may_be_null": false, + "map_bit": 5, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t_outer_1", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + }, + { + "table": "t_outer_2", + "table_scan": { + "rows": 9, + "cost": 2.0154 + } + }, + { + "table": "t_inner_2", + "table_scan": { + "rows": 9, + "cost": 2.0154 + } + }, + { + "table": "t_inner_1", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + }, + { + "table": "t_inner_3", + "table_scan": { + "rows": 9, + "cost": 2.0154 + } + }, + { + "table": "t_inner_4", + "table_scan": { + "rows": 3, + "cost": 2.0051 + } + } + ] + }, + { + "execution_plan_for_potential_materialization": { + "steps": [ + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_inner_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + } + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_inner_4"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + } + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + } + ] + } + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t_outer_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + } + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_outer_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": ["t_outer_1"], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "plan_prefix": [], + "table": "t_inner_1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_outer_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + }, + { + "plan_prefix": [], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "pruned_by_heuristic": true + } + ] + }, + { + "fix_semijoin_strategies_for_picked_join_order": [ + { + "semi_join_strategy": "sj_materialize", + "join_order": [ + { + "table": "t_inner_4" + }, + { + "table": "t_inner_3" + } + ] + }, + { + "semi_join_strategy": "sj_materialize", + "join_order": [ + { + "table": "t_inner_1" + }, + { + "table": "t_inner_2" + } + ] + } + ] + }, + { + "condition_on_constant_tables": "1" + }, + { + "attaching_conditions_to_tables": { + "original_condition": "1", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t_outer_1", + "attached": null + }, + { + "table": "t_inner_1", + "attached": null + }, + { + "table": "t_inner_2", + "attached": null + }, + { + "table": "<subquery2>", + "attached": null + }, + { + "table": "t_outer_2", + "attached": null + }, + { + "table": "t_inner_4", + "attached": null + }, + { + "table": "t_inner_3", + "attached": null + }, + { + "table": "<subquery3>", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +set @@optimizer_switch= @save_optimizer_switch; +drop table t1,t2; +set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index f55cf57b82a..e59a11fbfc3 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -323,6 +323,7 @@ set optimizer_trace='enabled=off'; --echo # MDEV-18528: Optimizer trace support for multi-table UPDATE and DELETE --echo # +set optimizer_trace=1; create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t0 (a int, b int); @@ -333,3 +334,43 @@ insert into t1 select * from t0; explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3; select * from information_schema.optimizer_trace; drop table ten,t0,t1; +set optimizer_trace='enabled=off'; + +--echo # +--echo # Merged to Materialized for derived tables +--echo # + +set optimizer_trace=1; +create table t1 (a int); +insert into t1 values (1),(2),(3); +explain select * from (select rand() from t1)q; +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +drop table t1; +set optimizer_trace='enabled=off'; + +--echo # +--echo # Semi-join nest +--echo # + +set optimizer_trace=1; +create table t1 (a int); +insert into t1 values (1),(2),(3); +create table t2(a int); +insert into t2 values (1),(2),(3),(1),(2),(3),(1),(2),(3); +set @save_optimizer_switch= @@optimizer_switch; +explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2); +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +--echo # with Firstmatch, mostly for tracing fix_semijoin_strategies_for_picked_join_order + +set optimizer_switch='materialization=off'; +explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and + t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +set optimizer_switch='materialization=on'; +explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and + t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +set @@optimizer_switch= @save_optimizer_switch; +drop table t1,t2; +set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index 855a7256f4f..50daef815d6 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -24,7 +24,7 @@ explain select * from t1 where a=1 or b=1 { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t1`.`filler` AS `filler` from `t1` where `t1`.`a` = 1 or `t1`.`b` = 1" + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 or t1.b = 1" } ] } @@ -194,15 +194,11 @@ explain select * from t1 where a=1 or b=1 { }, { "selectivity_for_indexes": [], - "selectivity_for_columns": [] + "selectivity_for_columns": [], + "cond_selectivity": 0.002 } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 43c9462303f..11ab89b1bb6 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -32,7 +32,7 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "select_id": 1, "steps": [ { - "expanded_query": "select `t1`.`pk1` AS `pk1`,`t1`.`pk2` AS `pk2`,`t1`.`key1` AS `key1`,`t1`.`key2` AS `key2` from `t1` where `t1`.`pk1` <> 0 and `t1`.`key1` = 1" + "expanded_query": "select t1.pk1 AS pk1,t1.pk2 AS pk2,t1.key1 AS key1,t1.key2 AS key2 from t1 where t1.pk1 <> 0 and t1.key1 = 1" } ] } @@ -183,15 +183,11 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "selectivity_from_index": 0.001 } ], - "selectivity_for_columns": [] + "selectivity_for_columns": [], + "cond_selectivity": 0.001 } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index 2d66a0a9576..9f5bacd6aa7 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -55,7 +55,7 @@ select * from db1.t1 { "select_id": 1, "steps": [ { - "expanded_query": "select `db1`.`t1`.`a` AS `a` from `t1`" + "expanded_query": "select db1.t1.a AS a from t1" } ] } @@ -85,11 +85,6 @@ select * from db1.t1 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { @@ -157,7 +152,7 @@ select * from db1.v1 { "view": { "table": "v1", "select_id": 2, - "merged": true + "algorithm": "merged" } }, { @@ -165,13 +160,13 @@ select * from db1.v1 { "select_id": 2, "steps": [ { - "expanded_query": "/* select#2 */ select `db1`.`t1`.`a` AS `a` from `t1`" + "expanded_query": "/* select#2 */ select db1.t1.a AS a from t1" } ] } }, { - "expanded_query": "/* select#1 */ select `db1`.`t1`.`a` AS `a` from `v1`" + "expanded_query": "/* select#1 */ select db1.t1.a AS a from v1" } ] } @@ -201,11 +196,6 @@ select * from db1.v1 { } ] }, - { - "execution_plan_for_potential_materialization": { - "steps": [] - } - }, { "considered_execution_plans": [ { diff --git a/sql/my_json_writer.cc b/sql/my_json_writer.cc index 7ae0c58bd7d..3755f8d4bcb 100644 --- a/sql/my_json_writer.cc +++ b/sql/my_json_writer.cc @@ -219,15 +219,15 @@ void Json_writer::add_str(const String &str) add_str(str.ptr(), str.length()); } -Json_writer_object::Json_writer_object(THD *thd) : +Json_writer_object::Json_writer_object(THD *thd) : Json_writer_struct(thd) { if (my_writer) my_writer->start_object(); } -Json_writer_object::Json_writer_object(THD* thd, const char *str) - : Json_writer_struct(thd) +Json_writer_object::Json_writer_object(THD* thd, const char *str) : + Json_writer_struct(thd) { if (my_writer) my_writer->add_member(str).start_object(); @@ -247,8 +247,8 @@ Json_writer_array::Json_writer_array(THD *thd) : my_writer->start_array(); } -Json_writer_array::Json_writer_array(THD *thd, const char *str) - :Json_writer_struct(thd) +Json_writer_array::Json_writer_array(THD *thd, const char *str) : + Json_writer_struct(thd) { if (my_writer) my_writer->add_member(str).start_array(); @@ -263,6 +263,16 @@ Json_writer_array::~Json_writer_array() } } +Json_writer_temp_disable::Json_writer_temp_disable(THD *thd_arg) +{ + thd= thd_arg; + thd->opt_trace.disable_tracing_if_required(); +} +Json_writer_temp_disable::~Json_writer_temp_disable() +{ + thd->opt_trace.enable_tracing_if_required(); +} + bool Single_line_formatting_helper::on_add_member(const char *name) { DBUG_ASSERT(state== INACTIVE || state == DISABLED); diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index 3234c748f5d..dbd7cd133e9 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -215,12 +215,11 @@ class Json_writer */ void set_size_limit(size_t mem_size) { output.set_size_limit(mem_size); } - // psergey: return how many bytes would be required to store everything size_t get_truncated_bytes() { return output.get_truncated_bytes(); } Json_writer() : indent_level(0), document_start(true), element_started(false), - first_child(true), allowed_mem_size(0) + first_child(true) { fmt_helper.init(this); } @@ -235,12 +234,6 @@ class Json_writer bool element_started; bool first_child; - /* - True when we are using the optimizer trace - FALSE otherwise - */ - size_t allowed_mem_size; - Single_line_formatting_helper fmt_helper; void append_indent(); @@ -566,6 +559,17 @@ class Json_writer_array : public Json_writer_struct ~Json_writer_array(); }; +/* + RAII-based class to disable writing into the JSON document +*/ + +class Json_writer_temp_disable +{ +public: + Json_writer_temp_disable(THD *thd_arg); + ~Json_writer_temp_disable(); + THD *thd; +}; /* RAII-based helper class to detect incorrect use of Json_writer. diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 468d16c095e..fcf0d2228a5 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -675,7 +675,7 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, { bool was_ored= FALSE; *is_last_check_pass= is_first_check_pass; - SEL_TREE** or_tree = trees; + SEL_TREE** or_tree= trees; for (uint i= 0; i < n_trees; i++, or_tree++) { SEL_TREE *result= 0; @@ -872,7 +872,7 @@ SEL_IMERGE::SEL_IMERGE(SEL_IMERGE *arg, uint cnt, trees_next= trees + (cnt ? cnt : arg->trees_next-arg->trees); trees_end= trees + elements; - for (SEL_TREE **tree = trees, **arg_tree= arg->trees; tree < trees_next; + for (SEL_TREE **tree= trees, **arg_tree= arg->trees; tree < trees_next; tree++, arg_tree++) { if (!(*tree= new SEL_TREE(*arg_tree, TRUE, param))) @@ -2211,7 +2211,7 @@ class TABLE_READ_PLAN @param trace_object The optimizer trace object the info is appended to */ virtual void trace_basic_info(const PARAM *param, - Json_writer_object *trace_object) const = 0; + Json_writer_object *trace_object) const= 0; }; @@ -2261,10 +2261,10 @@ void TRP_RANGE::trace_basic_info(const PARAM *param, Json_writer_object *trace_object) const { DBUG_ASSERT(param->using_real_indexes); - const uint keynr_in_table = param->real_keynr[key_idx]; + const uint keynr_in_table= param->real_keynr[key_idx]; - const KEY &cur_key = param->table->key_info[keynr_in_table]; - const KEY_PART_INFO *key_part = cur_key.key_part; + const KEY &cur_key= param->table->key_info[keynr_in_table]; + const KEY_PART_INFO *key_part= cur_key.key_part; trace_object->add("type", "range_scan") .add("index", cur_key.name) @@ -2329,7 +2329,7 @@ void TRP_ROR_UNION::trace_basic_info(const PARAM *param, THD *thd= param->thd; trace_object->add("type", "index_roworder_union"); Json_writer_array smth_trace(thd, "union_of"); - for (TABLE_READ_PLAN **current = first_ror; current != last_ror; current++) + for (TABLE_READ_PLAN **current= first_ror; current != last_ror; current++) { Json_writer_object trp_info(thd); (*current)->trace_basic_info(param, &trp_info); @@ -2364,7 +2364,7 @@ void TRP_INDEX_INTERSECT::trace_basic_info(const PARAM *param, THD *thd= param->thd; trace_object->add("type", "index_sort_intersect"); Json_writer_array smth_trace(thd, "index_sort_intersect_of"); - for (TRP_RANGE **current = range_scans; current != range_scans_end; + for (TRP_RANGE **current= range_scans; current != range_scans_end; current++) { Json_writer_object trp_info(thd); @@ -2466,9 +2466,9 @@ void TRP_GROUP_MIN_MAX::trace_basic_info(const PARAM *param, trace_object->add("type", "index_group").add("index", index_info->name); if (min_max_arg_part) - trace_object->add("group_attribute", min_max_arg_part->field->field_name); + trace_object->add("min_max_arg", min_max_arg_part->field->field_name); else - trace_object->add_null("group_attribute"); + trace_object->add_null("min_max_arg"); trace_object->add("min_aggregate", have_min) .add("max_aggregate", have_max) @@ -2476,12 +2476,12 @@ void TRP_GROUP_MIN_MAX::trace_basic_info(const PARAM *param, .add("rows", records) .add("cost", read_cost); - const KEY_PART_INFO *key_part = index_info->key_part; + const KEY_PART_INFO *key_part= index_info->key_part; { Json_writer_array trace_keyparts(thd, "key_parts_used_for_access"); - for (uint partno = 0; partno < used_key_parts; partno++) + for (uint partno= 0; partno < used_key_parts; partno++) { - const KEY_PART_INFO *cur_key_part = key_part + partno; + const KEY_PART_INFO *cur_key_part= key_part + partno; trace_keyparts.add(cur_key_part->field->field_name); } } @@ -3438,7 +3438,7 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) { rows= 0; table->reginfo.impossible_range= 1; - selectivity_for_column.add("selectivity_from_histograms", rows); + selectivity_for_column.add("selectivity_from_histogram", rows); selectivity_for_column.add("cause", "impossible range"); goto free_alloc; } @@ -3448,7 +3448,7 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) if (rows != DBL_MAX) { key->field->cond_selectivity= rows/table_records; - selectivity_for_column.add("selectivity_from_histograms", + selectivity_for_column.add("selectivity_from_histogram", key->field->cond_selectivity); } } @@ -3472,6 +3472,7 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) free_root(&alloc, MYF(0)); } + selectivity_for_columns.end(); if (quick && (quick->get_type() == QUICK_SELECT_I::QS_TYPE_ROR_UNION || quick->get_type() == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)) @@ -3546,7 +3547,7 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond) table->cond_selectivity_sampling_explain= &dt->list; } } - + trace_wrapper.add("cond_selectivity", table->cond_selectivity); DBUG_RETURN(FALSE); } @@ -5073,7 +5074,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, trace_idx.add("chosen", false).add("cause", "cost"); continue; } - const uint keynr_in_table = param->real_keynr[(*cur_child)->key_idx]; + const uint keynr_in_table= param->real_keynr[(*cur_child)->key_idx]; imerge_cost += (*cur_child)->read_cost; all_scans_ror_able &= ((*ptree)->n_ror_scans > 0); all_scans_rors &= (*cur_child)->is_ror; @@ -5134,7 +5135,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, */ double rid_comp_cost= static_cast<double>(non_cpk_scan_records) / TIME_FOR_COMPARE_ROWID; - imerge_cost += rid_comp_cost; + imerge_cost+= rid_comp_cost; trace_best_disjunct.add("cost_of_mapping_rowid_in_non_clustered_pk_scan", rid_comp_cost); } @@ -5142,7 +5143,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, /* Calculate cost(rowid_to_row_scan) */ { double sweep_cost= get_sweep_read_cost(param, non_cpk_scan_records); - imerge_cost += sweep_cost; + imerge_cost+= sweep_cost; trace_best_disjunct.add("cost_sort_rowid_and_read_disk", sweep_cost); } DBUG_PRINT("info",("index_merge cost with rowid-to-row scan: %g", @@ -5169,7 +5170,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge, } { - const double dup_removal_cost = Unique::get_use_cost( + const double dup_removal_cost= Unique::get_use_cost( param->imerge_cost_buff, (uint)non_cpk_scan_records, param->table->file->ref_length, (size_t)param->thd->variables.sortbuff_size, @@ -6371,11 +6372,11 @@ void TRP_ROR_INTERSECT::trace_basic_info(const PARAM *param, trace_object->add("clustered_pk_scan", cpk_scan != NULL); Json_writer_array smth_trace(thd, "intersect_of"); - for (ROR_SCAN_INFO **cur_scan = first_scan; cur_scan != last_scan; + for (ROR_SCAN_INFO **cur_scan= first_scan; cur_scan != last_scan; cur_scan++) { - const KEY &cur_key = param->table->key_info[(*cur_scan)->keynr]; - const KEY_PART_INFO *key_part = cur_key.key_part; + const KEY &cur_key= param->table->key_info[(*cur_scan)->keynr]; + const KEY_PART_INFO *key_part= cur_key.key_part; Json_writer_object trace_isect_idx(thd); trace_isect_idx.add("type", "range_scan"); @@ -6383,15 +6384,15 @@ void TRP_ROR_INTERSECT::trace_basic_info(const PARAM *param, trace_isect_idx.add("rows", (*cur_scan)->records); Json_writer_array trace_range(thd, "ranges"); - for (const SEL_ARG *current = (*cur_scan)->sel_arg->first(); current; - current = current->next) + for (const SEL_ARG *current= (*cur_scan)->sel_arg->first(); current; + current= current->next) { String range_info; range_info.set_charset(system_charset_info); - for (const SEL_ARG *part = current; part; - part = part->next_key_part ? part->next_key_part : nullptr) + for (const SEL_ARG *part= current; part; + part= part->next_key_part ? part->next_key_part : nullptr) { - const KEY_PART_INFO *cur_key_part = key_part + part->part; + const KEY_PART_INFO *cur_key_part= key_part + part->part; append_range(&range_info, cur_key_part, part->min_value, part->max_value, part->min_flag | part->max_flag); } @@ -6816,7 +6817,7 @@ static bool ror_intersect_add(ROR_INTERSECT_INFO *info, */ const double idx_cost= rows2double(info->index_records) / TIME_FOR_COMPARE_ROWID; - info->index_scan_costs += idx_cost; + info->index_scan_costs+= idx_cost; trace_costs->add("index_scan_cost", idx_cost); } else @@ -6840,7 +6841,7 @@ static bool ror_intersect_add(ROR_INTERSECT_INFO *info, { double sweep_cost= get_sweep_read_cost(info->param, double2rows(info->out_rows)); - info->total_cost += sweep_cost; + info->total_cost+= sweep_cost; trace_costs->add("disk_sweep_cost", sweep_cost); DBUG_PRINT("info", ("info->total_cost= %g", info->total_cost)); } @@ -7371,8 +7372,8 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, { Json_writer_array trace_range(thd, "ranges"); - const KEY &cur_key = param->table->key_info[keynr]; - const KEY_PART_INFO *key_part = cur_key.key_part; + const KEY &cur_key= param->table->key_info[keynr]; + const KEY_PART_INFO *key_part= cur_key.key_part; String range_info; range_info.set_charset(system_charset_info); @@ -13384,7 +13385,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) key_part_nr= get_field_keypart(cur_index_info, min_max_arg_item->field); if (key_part_nr <= cur_group_key_parts) { - cause = "aggregate column not suffix in idx"; + cause= "aggregate column not suffix in idx"; goto next_index; } min_max_arg_part= cur_index_info->key_part + key_part_nr - 1; @@ -13438,7 +13439,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) &cur_key_infix_len, &first_non_infix_part)) { - cause = "nonconst equality gap attribute"; + cause= "nonconst equality gap attribute"; goto next_index; } } @@ -13449,7 +13450,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) There is a gap but no range tree, thus no predicates at all for the non-group keyparts. */ - cause = "no nongroup keypart predicate"; + cause= "no nongroup keypart predicate"; goto next_index; } else if (first_non_group_part && join->conds) @@ -13474,7 +13475,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) if (join->conds->walk(&Item::find_item_in_field_list_processor, 0, key_part_range)) { - cause = "keypart reference from where clause"; + cause= "keypart reference from where clause"; goto next_index; } } @@ -13492,7 +13493,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) { if (bitmap_is_set(table->read_set, cur_part->field->field_index)) { - cause = "keypart after infix in query"; + cause= "keypart after infix in query"; goto next_index; } } @@ -13511,7 +13512,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) index_range_tree, &cur_range) || (cur_range && cur_range->type != SEL_ARG::KEY_RANGE)) { - cause = "minmax keypart in disjunctive query"; + cause= "minmax keypart in disjunctive query"; goto next_index; } } @@ -13538,7 +13539,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) { Json_writer_array trace_range(thd, "ranges"); - const KEY_PART_INFO *key_part = cur_index_info->key_part; + const KEY_PART_INFO *key_part= cur_index_info->key_part; String range_info; range_info.set_charset(system_charset_info); @@ -13578,7 +13579,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) if (cause) { trace_idx.add("usable", false).add("cause", cause); - cause = NULL; + cause= NULL; } } @@ -15776,9 +15777,9 @@ static void append_range_all_keyparts(Json_writer_array *range_trace, DBUG_ASSERT(keypart && keypart != &null_element); // Navigate to first interval in red-black tree - const KEY_PART_INFO *cur_key_part = key_parts + keypart->part; - const SEL_ARG *keypart_range = keypart->first(); - const size_t save_range_so_far_length = range_so_far->length(); + const KEY_PART_INFO *cur_key_part= key_parts + keypart->part; + const SEL_ARG *keypart_range= keypart->first(); + const size_t save_range_so_far_length= range_so_far->length(); while (keypart_range) @@ -15827,9 +15828,10 @@ static void append_range_all_keyparts(Json_writer_array *range_trace, static void print_key_value(String *out, const KEY_PART_INFO *key_part, const uchar *key) { - Field *field = key_part->field; + Field *field= key_part->field; - if (field->flags & BLOB_FLAG) { + if (field->flags & BLOB_FLAG) + { // Byte 0 of a nullable key is the null-byte. If set, key is NULL. if (field->real_maybe_null() && *key) out->append(STRING_WITH_LEN("NULL")); @@ -15840,7 +15842,7 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part, return; } - uint store_length = key_part->store_length; + uint store_length= key_part->store_length; if (field->real_maybe_null()) { @@ -15849,7 +15851,8 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part, Otherwise, print the key value starting immediately after the null-byte */ - if (*key) { + if (*key) + { out->append(STRING_WITH_LEN("NULL")); return; } @@ -15862,9 +15865,11 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part, optimizer trace expects. If the column is binary, the hex representation is printed to the trace instead. */ - if (field->flags & BINARY_FLAG) { + if (field->flags & BINARY_FLAG) + { out->append("0x"); - for (uint i = 0; i < store_length; i++) { + for (uint i = 0; i < store_length; i++) + { out->append(_dig_vec_lower[*(key + i) >> 4]); out->append(_dig_vec_lower[*(key + i) & 0x0F]); } @@ -15872,7 +15877,7 @@ static void print_key_value(String *out, const KEY_PART_INFO *key_part, } StringBuffer<128> tmp(system_charset_info); - TABLE *table = field->table; + TABLE *table= field->table; my_bitmap_map *old_sets[2]; dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set); diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 228fcd0f7e6..247452cc8f1 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -456,6 +456,7 @@ void best_access_path(JOIN *join, JOIN_TAB *s, table_map remaining_tables, uint idx, bool disable_jbuf, double record_count, POSITION *pos, POSITION *loose_scan_pos); +void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables); static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm, Item_in_subselect *subq_pred); @@ -697,9 +698,10 @@ int check_and_do_in_subquery_rewrites(JOIN *join) if (arena) thd->restore_active_arena(arena, &backup); in_subs->is_registered_semijoin= TRUE; - OPT_TRACE_TRANSFORM(thd, oto0, oto1, select_lex->select_number, + OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform, + select_lex->select_number, "IN (SELECT)", "semijoin"); - oto1.add("chosen", true); + trace_transform.add("chosen", true); } } else @@ -840,7 +842,7 @@ bool subquery_types_allow_materialization(THD* thd, Item_in_subselect *in_subs) in_subs->types_allow_materialization= FALSE; // Assign default values in_subs->sjm_scan_allowed= FALSE; - OPT_TRACE_TRANSFORM(thd, oto0, oto1, + OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform, in_subs->get_select_lex()->select_number, "IN (SELECT)", "materialization"); @@ -856,8 +858,8 @@ bool subquery_types_allow_materialization(THD* thd, Item_in_subselect *in_subs) if (!inner->type_handler()->subquery_type_allows_materialization(inner, outer)) { - oto1.add("possible", false); - oto1.add("cause", "types mismatch"); + trace_transform.add("possible", false); + trace_transform.add("cause", "types mismatch"); DBUG_RETURN(FALSE); } } @@ -879,12 +881,12 @@ bool subquery_types_allow_materialization(THD* thd, Item_in_subselect *in_subs) { in_subs->types_allow_materialization= TRUE; in_subs->sjm_scan_allowed= all_are_fields; - oto1.add("sjm_scan_allowed", all_are_fields) - .add("possible", true); + trace_transform.add("sjm_scan_allowed", all_are_fields) + .add("possible", true); DBUG_PRINT("info",("subquery_types_allow_materialization: ok, allowed")); DBUG_RETURN(TRUE); } - oto1.add("possible", false).add("cause", cause); + trace_transform.add("possible", false).add("cause", cause); DBUG_RETURN(FALSE); } @@ -1236,29 +1238,30 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) /* Stop processing if we've reached a subquery that's attached to the ON clause */ if (in_subq->do_not_convert_to_sj) { - OPT_TRACE_TRANSFORM(thd, oto0, oto1, + OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform, in_subq->get_select_lex()->select_number, "IN (SELECT)", "semijoin"); - oto1.add("converted_to_semi_join", false) - .add("cause", "subquery attached to the ON clause"); + trace_transform.add("converted_to_semi_join", false) + .add("cause", "subquery attached to the ON clause"); break; } if (in_subq->is_flattenable_semijoin) { - OPT_TRACE_TRANSFORM(thd, oto0, oto1, + OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform, in_subq->get_select_lex()->select_number, "IN (SELECT)", "semijoin"); if (join->table_count + in_subq->unit->first_select()->join->table_count >= MAX_TABLES) { - oto1.add("converted_to_semi_join", false); - oto1.add("cause", "table in parent join now exceeds MAX_TABLES"); + trace_transform.add("converted_to_semi_join", false); + trace_transform.add("cause", + "table in parent join now exceeds MAX_TABLES"); break; } if (convert_subq_to_sj(join, in_subq)) goto restore_arena_and_fail; - oto1.add("converted_to_semi_join", true); + trace_transform.add("converted_to_semi_join", true); } else { @@ -2380,6 +2383,8 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map) THD *thd= join->thd; List_iterator<TABLE_LIST> sj_list_it(join->select_lex->sj_nests); TABLE_LIST *sj_nest; + if (!join->select_lex->sj_nests.elements) + DBUG_RETURN(FALSE); Json_writer_object wrapper(thd); Json_writer_object trace_semijoin_nest(thd, "execution_plan_for_potential_materialization"); @@ -2939,6 +2944,7 @@ bool Sj_materialization_picker::check_qep(JOIN *join, { bool sjm_scan; SJ_MATERIALIZATION_INFO *mat_info; + THD *thd= join->thd; if ((mat_info= at_sjmat_pos(join, remaining_tables, new_join_tab, idx, &sjm_scan))) { @@ -3040,6 +3046,7 @@ bool Sj_materialization_picker::check_qep(JOIN *join, POSITION curpos, dummy; /* Need to re-run best-access-path as we prefix_rec_count has changed */ bool disable_jbuf= (join->thd->variables.join_cache_level == 0); + Json_writer_temp_disable trace_semijoin_mat_scan(thd); for (i= first_tab + mat_info->tables; i <= idx; i++) { best_access_path(join, join->positions[i].table, rem_tables, i, @@ -3590,6 +3597,12 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) table_map handled_tabs= 0; join->sjm_lookup_tables= 0; join->sjm_scan_tables= 0; + THD *thd= join->thd; + if (!join->select_lex->sj_nests.elements) + return; + Json_writer_object trace_wrapper(thd); + Json_writer_array trace_semijoin_strategies(thd, + "fix_semijoin_strategies_for_picked_join_order"); for (tablenr= table_count - 1 ; tablenr != join->const_tables - 1; tablenr--) { POSITION *pos= join->best_positions + tablenr; @@ -3614,8 +3627,18 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) first= tablenr - sjm->tables + 1; join->best_positions[first].n_sj_tables= sjm->tables; join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE; + Json_writer_object semijoin_strategy(thd); + semijoin_strategy.add("semi_join_strategy","sj_materialize"); + Json_writer_array semijoin_plan(thd, "join_order"); for (uint i= first; i < first+ sjm->tables; i++) + { + if (unlikely(thd->trace_started())) + { + Json_writer_object trace_one_table(thd); + trace_one_table.add_table_name(join->best_positions[i].table); + } join->sjm_lookup_tables |= join->best_positions[i].table->table->map; + } } else if (pos->sj_strategy == SJ_OPT_MATERIALIZE_SCAN) { @@ -3653,8 +3676,16 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) POSITION dummy; join->cur_sj_inner_tables= 0; + Json_writer_object semijoin_strategy(thd); + semijoin_strategy.add("semi_join_strategy","sj_materialize_scan"); + Json_writer_array semijoin_plan(thd, "join_order"); for (i= first + sjm->tables; i <= tablenr; i++) { + if (unlikely(thd->trace_started())) + { + Json_writer_object trace_one_table(thd); + trace_one_table.add_table_name(join->best_positions[i].table); + } best_access_path(join, join->best_positions[i].table, rem_tables, i, FALSE, prefix_rec_count, join->best_positions + i, &dummy); @@ -3683,8 +3714,16 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) join buffering */ join->cur_sj_inner_tables= 0; + Json_writer_object semijoin_strategy(thd); + semijoin_strategy.add("semi_join_strategy","firstmatch"); + Json_writer_array semijoin_plan(thd, "join_order"); for (idx= first; idx <= tablenr; idx++) { + if (unlikely(thd->trace_started())) + { + Json_writer_object trace_one_table(thd); + trace_one_table.add_table_name(join->best_positions[idx].table); + } if (join->best_positions[idx].use_join_buffer) { best_access_path(join, join->best_positions[idx].table, @@ -3713,8 +3752,16 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) join buffering */ join->cur_sj_inner_tables= 0; + Json_writer_object semijoin_strategy(thd); + semijoin_strategy.add("semi_join_strategy","sj_materialize"); + Json_writer_array semijoin_plan(thd, "join_order"); for (idx= first; idx <= tablenr; idx++) { + if (unlikely(thd->trace_started())) + { + Json_writer_object trace_one_table(thd); + trace_one_table.add_table_name(join->best_positions[idx].table); + } if (join->best_positions[idx].use_join_buffer || (idx == first)) { best_access_path(join, join->best_positions[idx].table, diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc index 03516146de2..422b21cb541 100644 --- a/sql/opt_table_elimination.cc +++ b/sql/opt_table_elimination.cc @@ -525,7 +525,7 @@ eliminate_tables_for_list(JOIN *join, table_map tables_in_list, Item *on_expr, table_map tables_used_elsewhere, - Json_writer_array* eliminate_tables); + Json_writer_array* trace_eliminate_tables); static bool check_func_dependency(JOIN *join, table_map dep_tables, @@ -545,7 +545,7 @@ Dep_module_expr *merge_eq_mods(Dep_module_expr *start, Dep_module_expr *new_fields, Dep_module_expr *end, uint and_level); static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl, - Json_writer_array* eliminate_tables); + Json_writer_array* trace_eliminate_tables); static void add_module_expr(Dep_analysis_context *dac, Dep_module_expr **eq_mod, uint and_level, Dep_value_field *field_val, Item *right, @@ -671,12 +671,12 @@ void eliminate_tables(JOIN *join) } table_map all_tables= join->all_tables_map(); - Json_writer_array eliminated_tables(thd,"eliminated_tables"); + Json_writer_array trace_eliminated_tables(thd,"eliminated_tables"); if (all_tables & ~used_tables) { /* There are some tables that we probably could eliminate. Try it. */ eliminate_tables_for_list(join, join->join_list, all_tables, NULL, - used_tables, &eliminated_tables); + used_tables, &trace_eliminated_tables); } DBUG_VOID_RETURN; } @@ -720,7 +720,7 @@ static bool eliminate_tables_for_list(JOIN *join, List<TABLE_LIST> *join_list, table_map list_tables, Item *on_expr, table_map tables_used_elsewhere, - Json_writer_array *eliminate_tables) + Json_writer_array *trace_eliminate_tables) { TABLE_LIST *tbl; List_iterator<TABLE_LIST> it(*join_list); @@ -742,9 +742,10 @@ eliminate_tables_for_list(JOIN *join, List<TABLE_LIST> *join_list, &tbl->nested_join->join_list, tbl->nested_join->used_tables, tbl->on_expr, - outside_used_tables, eliminate_tables)) + outside_used_tables, + trace_eliminate_tables)) { - mark_as_eliminated(join, tbl, eliminate_tables); + mark_as_eliminated(join, tbl, trace_eliminate_tables); } else all_eliminated= FALSE; @@ -756,7 +757,7 @@ eliminate_tables_for_list(JOIN *join, List<TABLE_LIST> *join_list, check_func_dependency(join, tbl->table->map, NULL, tbl, tbl->on_expr)) { - mark_as_eliminated(join, tbl, eliminate_tables); + mark_as_eliminated(join, tbl, trace_eliminate_tables); } else all_eliminated= FALSE; @@ -1797,7 +1798,7 @@ Dep_module* Dep_value_field::get_next_unbound_module(Dep_analysis_context *dac, */ static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl, - Json_writer_array* eliminate_tables) + Json_writer_array* trace_eliminate_tables) { TABLE *table; /* @@ -1810,7 +1811,7 @@ static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl, TABLE_LIST *child; List_iterator<TABLE_LIST> it(tbl->nested_join->join_list); while ((child= it++)) - mark_as_eliminated(join, child, eliminate_tables); + mark_as_eliminated(join, child, trace_eliminate_tables); } else if ((table= tbl->table)) { @@ -1821,7 +1822,7 @@ static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl, tab->type= JT_CONST; tab->table->const_table= 1; join->eliminated_tables |= table->map; - eliminate_tables->add(table->alias.c_ptr_safe()); + trace_eliminate_tables->add(table->alias.c_ptr_safe()); join->const_table_map|= table->map; set_position(join, join->const_tables++, tab, (KEYUSE*)0); } diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index ca05f36579a..befc7934a3a 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -24,7 +24,7 @@ #include "my_json_writer.h" #include "sp_head.h" -const char I_S_table_name[] = "OPTIMIZER_TRACE"; +const char I_S_table_name[]= "OPTIMIZER_TRACE"; /** Whether a list of tables contains information_schema.OPTIMIZER_TRACE. @@ -38,7 +38,7 @@ const char I_S_table_name[] = "OPTIMIZER_TRACE"; */ bool list_has_optimizer_trace_table(const TABLE_LIST *tbl) { - for (; tbl; tbl = tbl->next_global) + for (; tbl; tbl= tbl->next_global) { if (tbl->schema_table && 0 == strcmp(tbl->schema_table->table_name, I_S_table_name)) @@ -59,14 +59,15 @@ bool sets_var_optimizer_trace(enum enum_sql_command sql_command, { List_iterator_fast<set_var_base> it(*set_vars); const set_var_base *var; - while ((var = it++)) + while ((var= it++)) if (var->is_var_optimizer_trace()) return true; } return false; } -ST_FIELD_INFO optimizer_trace_info[] = { +ST_FIELD_INFO optimizer_trace_info[]= +{ /* name, length, type, value, maybe_null, old_name, open_method */ {"QUERY", 65535, MYSQL_TYPE_STRING, 0, false, NULL, SKIP_OPEN_TABLE}, {"TRACE", 65535, MYSQL_TYPE_STRING, 0, false, NULL, SKIP_OPEN_TABLE}, @@ -74,12 +75,13 @@ ST_FIELD_INFO optimizer_trace_info[] = { SKIP_OPEN_TABLE}, {"INSUFFICIENT_PRIVILEGES", 1, MYSQL_TYPE_TINY, 0, false, NULL, SKIP_OPEN_TABLE}, - {NULL, 0, MYSQL_TYPE_STRING, 0, true, NULL, 0}}; + {NULL, 0, MYSQL_TYPE_STRING, 0, true, NULL, 0} +}; /* TODO: one-line needs to be implemented seperately */ -const char *Opt_trace_context::flag_names[] = {"enabled", "default", +const char *Opt_trace_context::flag_names[]= {"enabled", "default", NullS}; /* @@ -105,15 +107,15 @@ void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex, { if (!thd->trace_started()) return; - char buff[1024]; - String str(buff, sizeof(buff), system_charset_info); - str.length(0); + StringBuffer<1024> str(system_charset_info); + ulonglong save_option_bits= thd->variables.option_bits; + thd->variables.option_bits &= ~OPTION_QUOTE_SHOW_CREATE; select_lex->print(thd, &str, enum_query_type(QT_TO_SYSTEM_CHARSET | QT_SHOW_SELECT_NUMBER | QT_ITEM_IDENT_SKIP_DB_NAMES | - QT_VIEW_INTERNAL - )); + QT_VIEW_INTERNAL)); + thd->variables.option_bits= save_option_bits; /* The output is not very pretty lots of back-ticks, the output is as the one in explain extended , lets try to improved it here. @@ -141,7 +143,7 @@ void opt_trace_disable_if_no_security_context_access(THD *thd) */ return; } - Opt_trace_context *const trace = &thd->opt_trace; + Opt_trace_context *const trace= &thd->opt_trace; if (!thd->trace_started()) { /* @@ -187,7 +189,6 @@ void opt_trace_disable_if_no_security_context_access(THD *thd) thd->main_security_ctx.priv_host, thd->security_context()->priv_host))) trace->missing_privilege(); - return; } void opt_trace_disable_if_no_stored_proc_func_access(THD *thd, sp_head *sp) @@ -197,17 +198,16 @@ void opt_trace_disable_if_no_stored_proc_func_access(THD *thd, sp_head *sp) thd->system_thread) return; - Opt_trace_context *const trace = &thd->opt_trace; + Opt_trace_context *const trace= &thd->opt_trace; if (!thd->trace_started()) return; bool full_access; - Security_context *const backup_thd_sctx = thd->security_context(); + Security_context *const backup_thd_sctx= thd->security_context(); thd->set_security_context(&thd->main_security_ctx); - const bool rc = check_show_routine_access(thd, sp, &full_access) || !full_access; + const bool rc= check_show_routine_access(thd, sp, &full_access) || !full_access; thd->set_security_context(backup_thd_sctx); if (rc) trace->missing_privilege(); - return; } /** @@ -231,16 +231,16 @@ void opt_trace_disable_if_no_tables_access(THD *thd, TABLE_LIST *tbl) if (likely(!(thd->variables.optimizer_trace & Opt_trace_context::FLAG_ENABLED)) || thd->system_thread) return; - Opt_trace_context *const trace = &thd->opt_trace; + Opt_trace_context *const trace= &thd->opt_trace; if (!thd->trace_started()) return; - Security_context *const backup_thd_sctx = thd->security_context(); + Security_context *const backup_thd_sctx= thd->security_context(); thd->set_security_context(&thd->main_security_ctx); - const TABLE_LIST *const first_not_own_table = thd->lex->first_not_own_table(); - for (TABLE_LIST *t = tbl; t != NULL && t != first_not_own_table; - t = t->next_global) + const TABLE_LIST *const first_not_own_table= thd->lex->first_not_own_table(); + for (TABLE_LIST *t= tbl; t != NULL && t != first_not_own_table; + t= t->next_global) { /* Anonymous derived tables (as in @@ -248,9 +248,9 @@ void opt_trace_disable_if_no_tables_access(THD *thd, TABLE_LIST *tbl) */ if (!t->is_anonymous_derived_table()) { - const GRANT_INFO backup_grant_info = t->grant; - Security_context *const backup_table_sctx = t->security_ctx; - t->security_ctx = NULL; + const GRANT_INFO backup_grant_info= t->grant; + Security_context *const backup_table_sctx= t->security_ctx; + t->security_ctx= NULL; /* (1) check_table_access() fills t->grant.privilege. (2) Because SELECT privileges can be column-based, @@ -271,8 +271,8 @@ void opt_trace_disable_if_no_tables_access(THD *thd, TABLE_LIST *tbl) */ rc |= check_table_access(thd, SHOW_VIEW_ACL, t, false, 1, true); } - t->security_ctx = backup_table_sctx; - t->grant = backup_grant_info; + t->security_ctx= backup_table_sctx; + t->grant= backup_grant_info; if (rc) { trace->missing_privilege(); @@ -292,22 +292,22 @@ void opt_trace_disable_if_no_view_access(THD *thd, TABLE_LIST *view, Opt_trace_context::FLAG_ENABLED)) || thd->system_thread) return; - Opt_trace_context *const trace = &thd->opt_trace; + Opt_trace_context *const trace= &thd->opt_trace; if (!thd->trace_started()) return; - Security_context *const backup_table_sctx = view->security_ctx; - Security_context *const backup_thd_sctx = thd->security_context(); - const GRANT_INFO backup_grant_info = view->grant; + Security_context *const backup_table_sctx= view->security_ctx; + Security_context *const backup_thd_sctx= thd->security_context(); + const GRANT_INFO backup_grant_info= view->grant; - view->security_ctx = NULL; // no SUID context for view + view->security_ctx= NULL; // no SUID context for view // no SUID context for THD thd->set_security_context(&thd->main_security_ctx); - const int rc = check_table_access(thd, SHOW_VIEW_ACL, view, false, 1, true); + const int rc= check_table_access(thd, SHOW_VIEW_ACL, view, false, 1, true); - view->security_ctx = backup_table_sctx; + view->security_ctx= backup_table_sctx; thd->set_security_context(backup_thd_sctx); - view->grant = backup_grant_info; + view->grant= backup_grant_info; if (rc) { @@ -347,16 +347,13 @@ class Opt_trace_stmt { ~Opt_trace_stmt() { delete current_json; - missing_priv= false; - ctx= NULL; - I_S_disabled= 0; } void set_query(const char *query_ptr, size_t length, const CHARSET_INFO *charset); void open_struct(const char *key, char opening_bracket); void close_struct(const char *saved_key, char closing_bracket); void fill_info(Opt_trace_info* info); void add(const char *key, char *opening_bracket, size_t val_length); - Json_writer* get_current_json(){return current_json;} + Json_writer* get_current_json() {return current_json;} void missing_privilege(); void disable_tracing_for_children(); void enable_tracing_for_children(); @@ -372,6 +369,12 @@ class Opt_trace_stmt { String query; // store the query sent by the user Json_writer *current_json; // stores the trace bool missing_priv; ///< whether user lacks privilege to see this trace + /* + 0 <=> this trace should be in information_schema. + !=0 tracing is disabled, this currently happens when we want to trace a + sub-statement. For now traces are only collect for the top statement + not for the sub-statments. + */ uint I_S_disabled; }; @@ -440,28 +443,11 @@ bool Opt_trace_context::is_enabled() Opt_trace_context::Opt_trace_context() { current_trace= NULL; - inited= FALSE; - traces= NULL; max_mem_size= 0; } Opt_trace_context::~Opt_trace_context() { - inited= FALSE; - /* - would be nice to move this to a function - */ - if (traces) - { - while (traces->elements()) - { - Opt_trace_stmt *prev= traces->at(0); - delete prev; - traces->del(0); - } - delete traces; - traces= NULL; - } - max_mem_size= 0; + delete_traces(); } void Opt_trace_context::set_query(const char *query, size_t length, const CHARSET_INFO *charset) @@ -487,26 +473,21 @@ void Opt_trace_context::start(THD *thd, TABLE_LIST *tbl, DBUG_ASSERT(!current_trace); current_trace= new Opt_trace_stmt(this); max_mem_size= max_mem_size_arg; - if (!inited) - { - traces= new Dynamic_array<Opt_trace_stmt*>(); - inited= TRUE; - } set_allowed_mem_size(remaining_mem_size()); } void Opt_trace_context::end() { if (current_trace) - traces->push(current_trace); + traces.push(current_trace); - if (!traces->elements()) + if (!traces.elements()) return; - if (traces->elements() > 1) + if (traces.elements() > 1) { - Opt_trace_stmt *prev= traces->at(0); + Opt_trace_stmt *prev= traces.at(0); delete prev; - traces->del(0); + traces.del(0); } current_trace= NULL; } @@ -522,7 +503,7 @@ Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl, if optimizer trace is enabled and the statment we have is traceable, then we start the context. */ - const ulonglong var = thd->variables.optimizer_trace; + const ulonglong var= thd->variables.optimizer_trace; traceable= FALSE; if (unlikely(var & Opt_trace_context::FLAG_ENABLED) && sql_command_can_be_traced(sql_command) && @@ -554,21 +535,21 @@ Opt_trace_start::~Opt_trace_start() void Opt_trace_stmt::fill_info(Opt_trace_info* info) { - if (unlikely(info->missing_priv = get_missing_priv())) + if (unlikely(info->missing_priv= get_missing_priv())) { - info->trace_ptr = info->query_ptr = ""; - info->trace_length = info->query_length = 0; - info->query_charset = &my_charset_bin; - info->missing_bytes = 0; + info->trace_ptr= info->query_ptr= ""; + info->trace_length= info->query_length= 0; + info->query_charset= &my_charset_bin; + info->missing_bytes= 0; } else { - info->trace_ptr = current_json->output.get_string()->ptr(); - info->trace_length = get_length(); - info->query_ptr = query.ptr(); - info->query_length = query.length(); - info->query_charset = query.charset(); - info->missing_bytes = get_truncated_bytes(); + info->trace_ptr= current_json->output.get_string()->ptr(); + info->trace_length= get_length(); + info->query_ptr= query.ptr(); + info->query_length= query.length(); + info->query_charset= query.charset(); + info->missing_bytes= get_truncated_bytes(); info->missing_priv= get_missing_priv(); } } @@ -659,9 +640,7 @@ void Json_writer::add_str(Item *item) if (item) { THD *thd= current_thd; - char buff[256]; - String str(buff, sizeof(buff), system_charset_info); - str.length(0); + StringBuffer<256> str(system_charset_info); ulonglong save_option_bits= thd->variables.option_bits; thd->variables.option_bits &= ~OPTION_QUOTE_SHOW_CREATE; @@ -675,26 +654,23 @@ void Json_writer::add_str(Item *item) add_null(); } -void Opt_trace_context::flush_optimizer_trace() +void Opt_trace_context::delete_traces() { - inited= false; - if (traces) + if (traces.elements()) { - while (traces->elements()) + while (traces.elements()) { - Opt_trace_stmt *prev= traces->at(0); + Opt_trace_stmt *prev= traces.at(0); delete prev; - traces->del(0); + traces.del(0); } - delete traces; - traces= NULL; } } int fill_optimizer_trace_info(THD *thd, TABLE_LIST *tables, Item *) { - TABLE *table = tables->table; + TABLE *table= tables->table; Opt_trace_info info; /* get_values of trace, query , missing bytes and missing_priv @@ -703,7 +679,7 @@ int fill_optimizer_trace_info(THD *thd, TABLE_LIST *tables, Item *) */ Opt_trace_context* ctx= &thd->opt_trace; - if (thd->opt_trace.empty()) + if (!thd->opt_trace.empty()) { Opt_trace_stmt *stmt= ctx->get_top_trace(); stmt->fill_info(&info); diff --git a/sql/opt_trace.h b/sql/opt_trace.h index 0e2d0146a49..52318bc6b7f 100644 --- a/sql/opt_trace.h +++ b/sql/opt_trace.h @@ -193,9 +193,16 @@ void opt_trace_disable_if_no_stored_proc_func_access(THD *thd, sp_head *sp); */ int fill_optimizer_trace_info(THD *thd, TABLE_LIST *tables, Item *); -#define OPT_TRACE_TRANSFORM(writer, object_level0, object_level1, \ +#define OPT_TRACE_TRANSFORM(thd, object_level0, object_level1, \ select_number, from, to) \ - Json_writer_object object_level0(writer); \ - Json_writer_object object_level1(writer, "transformation"); \ + Json_writer_object object_level0(thd); \ + Json_writer_object object_level1(thd, "transformation"); \ object_level1.add_select_number(select_number).add("from", from).add("to", to); -#endif \ No newline at end of file + +#define OPT_TRACE_VIEWS_TRANSFORM(thd, object_level0, object_level1, \ + derived, name, select_number, algorithm) \ + Json_writer_object trace_wrapper(thd); \ + Json_writer_object trace_derived(thd, derived); \ + trace_derived.add("table", name).add_select_number(select_number) \ + .add("algorithm", algorithm); +#endif diff --git a/sql/opt_trace_context.h b/sql/opt_trace_context.h index 87317f67e22..e5df16b1e3b 100644 --- a/sql/opt_trace_context.h +++ b/sql/opt_trace_context.h @@ -19,14 +19,14 @@ class Opt_trace_context ulong max_mem_size_arg); void end(); void set_query(const char *query, size_t length, const CHARSET_INFO *charset); - void flush_optimizer_trace(); + void delete_traces(); void set_allowed_mem_size(size_t mem_size); size_t remaining_mem_size(); private: Opt_trace_stmt* top_trace() { - return *(traces->front()); + return *(traces.front()); } public: @@ -39,7 +39,7 @@ class Opt_trace_context Opt_trace_stmt* get_top_trace() { - if (!traces || !traces->elements()) + if (!traces.elements()) return NULL; return top_trace(); } @@ -52,7 +52,7 @@ class Opt_trace_context bool empty() { - return traces && (static_cast<uint>(traces->elements()) != 0); + return static_cast<uint>(traces.elements()) == 0; } bool is_started() @@ -79,13 +79,8 @@ class Opt_trace_context /* List of traces (currently it stores only 1 trace) */ - Dynamic_array<Opt_trace_stmt*> *traces; + Dynamic_array<Opt_trace_stmt*> traces; Opt_trace_stmt *current_trace; - /* - TRUE: if we allocate memory for list of traces - FALSE: otherwise - */ - bool inited; size_t max_mem_size; }; diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 891a64a5748..bb0227df212 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -1411,7 +1411,7 @@ void THD::change_user(void) sp_cache_clear(&sp_func_cache); sp_cache_clear(&sp_package_spec_cache); sp_cache_clear(&sp_package_body_cache); - opt_trace.flush_optimizer_trace(); + opt_trace.delete_traces(); } /** diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index a3a53320ac0..6bfcd40f411 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -34,6 +34,7 @@ #include "sql_class.h" #include "sql_cte.h" #include "my_json_writer.h" +#include "opt_trace.h" typedef bool (*dt_processor)(THD *thd, LEX *lex, TABLE_LIST *derived); @@ -384,6 +385,15 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) { /* There is random function => fall back to materialization. */ cause= "Random function in the select"; + if (unlikely(thd->trace_started())) + { + OPT_TRACE_VIEWS_TRANSFORM(thd, trace_wrapper, trace_derived, + derived->is_derived() ? "derived" : "view", + derived->alias.str ? derived->alias.str : "<NULL>", + derived->get_unit()->first_select()->select_number, + "materialized"); + trace_derived.add("cause", cause); + } derived->change_refs_to_fields(); derived->set_materialized_derived(); DBUG_RETURN(FALSE); @@ -497,19 +507,12 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) if (unlikely(thd->trace_started())) { - /* - Add to the optimizer trace the change in choice for merged - derived tables/views to materialised ones. - */ - Json_writer_object trace_wrapper(thd); - Json_writer_object trace_derived(thd, derived->is_derived() ? - "derived" : "view"); - trace_derived.add("table", derived->alias.str ? derived->alias.str : "<NULL>") - .add_select_number(derived->get_unit()-> - first_select()->select_number) - .add("initial_choice", "merged") - .add("final_choice", "materialized") - .add("cause", cause); + OPT_TRACE_VIEWS_TRANSFORM(thd,trace_wrapper, trace_derived, + derived->is_derived() ? "derived" : "view", + derived->alias.str ? derived->alias.str : "<NULL>", + derived->get_unit()->first_select()->select_number, + "materialized"); + trace_derived.add("cause", cause); } derived->change_refs_to_fields(); @@ -778,15 +781,11 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) Add to optimizer trace whether a derived table/view is merged into the parent select or not. */ - Json_writer_object trace_wrapper(thd); - Json_writer_object trace_derived(thd, derived->is_derived() ? - "derived" : "view"); - trace_derived.add("table", derived->alias.str ? derived->alias.str : "<NULL>") - .add_select_number(derived->get_unit()->first_select()->select_number); - if (derived->is_materialized_derived()) - trace_derived.add("materialized", true); - if (derived->is_merged_derived()) - trace_derived.add("merged", true); + OPT_TRACE_VIEWS_TRANSFORM(thd, trace_wrapper, trace_derived, + derived->is_derived() ? "derived" : "view", + derived->alias.str ? derived->alias.str : "<NULL>", + derived->get_unit()->first_select()->select_number, + derived->is_merged_derived() ? "merged" : "materialized"); } /* Above cascade call of prepare is important for PS protocol, but after it diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3670eff574e..d08f8fe4b29 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -121,6 +121,7 @@ static bool best_extension_by_limited_search(JOIN *join, double read_time, uint depth, uint prune_level, uint use_cond_selectivity); +void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables); static uint determine_search_depth(JOIN* join); C_MODE_START static int join_tab_cmp(const void *dummy, const void* ptr1, const void* ptr2); @@ -302,8 +303,6 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab); static Item **get_sargable_cond(JOIN *join, TABLE *table); -static void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables); - #ifndef DBUG_OFF /* @@ -359,16 +358,16 @@ static void trace_table_dependencies(THD *thd, { Json_writer_object trace_wrapper(thd); Json_writer_array trace_dep(thd, "table_dependencies"); - for (uint i = 0; i < table_count; i++) + for (uint i= 0; i < table_count; i++) { - TABLE_LIST *table_ref = join_tabs[i].tab_list; + TABLE_LIST *table_ref= join_tabs[i].tab_list; Json_writer_object trace_one_table(thd); trace_one_table.add_table_name(&join_tabs[i]); trace_one_table.add("row_may_be_null", (bool)table_ref->table->maybe_null); - const table_map map = table_ref->get_map(); + const table_map map= table_ref->get_map(); DBUG_ASSERT(map < (1ULL << table_count)); - for (uint j = 0; j < table_count; j++) + for (uint j= 0; j < table_count; j++) { if (map & (1ULL << j)) { @@ -377,14 +376,10 @@ static void trace_table_dependencies(THD *thd, } } Json_writer_array depends_on(thd, "depends_on_map_bits"); - static_assert(sizeof(table_ref->get_map()) <= 64, - "RAND_TABLE_BIT may be in join_tabs[i].dependent, so we test " - "all 64 bits."); - for (uint j = 0; j < 64; j++) - { - if (join_tabs[i].dependent & (1ULL << j)) - depends_on.add(static_cast<longlong>(j)); - } + Table_map_iterator it(join_tabs[i].dependent); + uint dep_bit; + while ((dep_bit= it++) != Table_map_iterator::BITMAP_END) + depends_on.add(static_cast<longlong>(dep_bit)); } } @@ -9045,13 +9040,13 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, } -static void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables) +void trace_plan_prefix(JOIN *join, uint idx, table_map remaining_tables) { - THD *const thd = join->thd; + THD *const thd= join->thd; Json_writer_array plan_prefix(thd, "plan_prefix"); - for (uint i = 0; i < idx; i++) + for (uint i= 0; i < idx; i++) { - TABLE_LIST *const tr = join->positions[i].table->tab_list; + TABLE_LIST *const tr= join->positions[i].table->tab_list; if (!(tr->map & remaining_tables)) plan_prefix.add_table_name(join->positions[i].table); } @@ -9261,9 +9256,6 @@ best_extension_by_limited_search(JOIN *join, current_record_count / (double) TIME_FOR_COMPARE - filter_cmp_gain; - /* - TODO add filtering estimates here - */ advance_sj_state(join, remaining_tables, idx, ¤t_record_count, ¤t_read_time, &loose_scan_pos); @@ -11070,12 +11062,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) tab->table->intersect_keys.is_set(tab->ref.key)))) { /* Range uses longer key; Use this instead of ref on key */ - - /* - We can trace here, changing ref access to range access here - have a range that uses longer key. - Lets take @spetrunia's opinion - */ Json_writer_object ref_to_range(thd); ref_to_range.add("ref_to_range", true); ref_to_range.add("cause", "range uses longer key"); @@ -16413,6 +16399,8 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, double cost, rec_count; table_map reopt_remaining_tables= last_remaining_tables; uint i; + THD *thd= join->thd; + Json_writer_temp_disable trace_wo_join_buffering(thd); if (first_tab > join->const_tables) { @@ -16447,7 +16435,7 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, { JOIN_TAB *rs= join->positions[i].table; POSITION pos, loose_scan_pos; - + if ((i == first_tab && first_alt) || join->positions[i].use_join_buffer) { /* Find the best access method that would not use join buffering */ diff --git a/sql/sql_test.cc b/sql/sql_test.cc index f247fb10f89..93085251711 100644 --- a/sql/sql_test.cc +++ b/sql/sql_test.cc @@ -665,11 +665,11 @@ void print_keyuse_array_for_trace(THD *thd, DYNAMIC_ARRAY *keyuse_array) KEYUSE *keyuse= (KEYUSE*)dynamic_array_ptr(keyuse_array, i); Json_writer_object keyuse_elem(thd); keyuse_elem.add_table_name(keyuse->table->reginfo.join_tab); - keyuse_elem.add("field", (keyuse->keypart == FT_KEYPART) ? "<fulltext>" - : (keyuse->is_for_hash_join() - ? keyuse->table->field[keyuse->keypart] - ->field_name.str - : keyuse->table->key_info[keyuse->key] + keyuse_elem.add("field", (keyuse->keypart == FT_KEYPART) ? "<fulltext>": + (keyuse->is_for_hash_join() ? + keyuse->table->field[keyuse->keypart] + ->field_name.str : + keyuse->table->key_info[keyuse->key] .key_part[keyuse->keypart] .field->field_name.str)); keyuse_elem.add("equals",keyuse->val);