summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorVarun Gupta <varun.gupta@mariadb.com>2019-09-04 19:51:27 +0530
committerVarun Gupta <varun.gupta@mariadb.com>2019-09-11 04:32:40 +0530
commit7b988e5cebf3c6979694056e474f8256b8466696 (patch)
tree618c6cff848b603b60f86149922e98ccf6a5730a
parente980cf91cd01313edb659b12854c420e1b064246 (diff)
downloadmariadb-git-7b988e5cebf3c6979694056e474f8256b8466696.tar.gz
MDEV-20444: More information regarding access of a table to be printed inside the optimizer_trace
Added: 1) estimated_join_cardinality 2) best_chosen_access_method for a table 3) best_join_order
Diffstat
-rw-r--r--mysql-test/main/opt_trace.result1310
-rw-r--r--mysql-test/main/opt_trace_index_merge.result17
-rw-r--r--mysql-test/main/opt_trace_index_merge_innodb.result15
-rw-r--r--mysql-test/main/opt_trace_security.result30
-rw-r--r--sql/opt_trace.cc39
-rw-r--r--sql/opt_trace.h3
-rw-r--r--sql/sql_select.cc46
7 files changed, 1279 insertions, 181 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 13110f0bc73..3438714fd28 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -135,14 +135,25 @@ select * from v1 {
"cost": 2.2044,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 1,
+ "cost": 2.2044,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 1,
- "cost_for_plan": 2.4044
+ "cost_for_plan": 2.4044,
+ "estimated_join_cardinality": 1
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.a = 1",
"attached_conditions_computation": [],
@@ -270,14 +281,25 @@ select * from (select * from t1 where t1.a=1)q {
"cost": 2.2044,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 1,
+ "cost": 2.2044,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 1,
- "cost_for_plan": 2.4044
+ "cost_for_plan": 2.4044,
+ "estimated_join_cardinality": 1
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.a = 1",
"attached_conditions_computation": [],
@@ -411,14 +433,25 @@ select * from v2 {
"chosen": true,
"use_tmp_table": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 1,
+ "cost": 2.2044,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 1,
- "cost_for_plan": 2.4044
+ "cost_for_plan": 2.4044,
+ "estimated_join_cardinality": 1
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.a = 1",
"attached_conditions_computation": [],
@@ -467,14 +500,25 @@ select * from v2 {
"cost": 2,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 2,
+ "cost": 2,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 2,
- "cost_for_plan": 2.4
+ "cost_for_plan": 2.4,
+ "estimated_join_cardinality": 2
}
]
},
{
+ "best_join_order": ["<derived2>"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -596,14 +640,25 @@ explain select * from v2 {
"cost": 2.022,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 10,
- "cost_for_plan": 4.022
+ "cost_for_plan": 4.022,
+ "estimated_join_cardinality": 10
}
]
},
{
+ "best_join_order": ["t2"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -705,14 +760,25 @@ explain select * from v1 {
"chosen": true,
"use_tmp_table": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 10,
- "cost_for_plan": 4.022
+ "cost_for_plan": 4.022,
+ "estimated_join_cardinality": 10
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -761,14 +827,25 @@ explain select * from v1 {
"cost": 10,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 10,
+ "cost": 10,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 10,
- "cost_for_plan": 12
+ "cost_for_plan": 12,
+ "estimated_join_cardinality": 10
}
]
},
{
+ "best_join_order": ["<derived2>"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -922,7 +999,14 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"cost": 2.3174,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 100,
+ "cost": 2.3174,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 100,
"cost_for_plan": 22.317,
@@ -947,10 +1031,18 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"cost": 2.3174,
"chosen": false
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "ref",
+ "records": 1,
+ "cost": 200,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 100,
- "cost_for_plan": 242.32
+ "cost_for_plan": 242.32,
+ "estimated_join_cardinality": 100
}
]
},
@@ -965,7 +1057,14 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"cost": 2.3174,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 100,
+ "cost": 2.3174,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 100,
"cost_for_plan": 22.317,
@@ -990,7 +1089,14 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"cost": 2.3174,
"chosen": false
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "ref",
+ "records": 1,
+ "cost": 200,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 100,
"cost_for_plan": 242.32,
@@ -1001,6 +1107,9 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
]
},
{
+ "best_join_order": ["t1", "t2"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t2.a = t1.b and t1.a = t2.b + 2",
"attached_conditions_computation": [],
@@ -1146,19 +1255,30 @@ EXPLAIN SELECT DISTINCT a FROM t1 {
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "range",
+ "access_type": "index_merge",
"resulting_rows": 5,
"cost": 6.75,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "index_merge",
+ "records": 5,
+ "cost": 6.75,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 5,
- "cost_for_plan": 7.75
+ "cost_for_plan": 7.75,
+ "estimated_join_cardinality": 5
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -1331,14 +1451,25 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a {
"chosen": true,
"use_tmp_table": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 0.5849,
+ "cost": 3.3121,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 0.5849,
- "cost_for_plan": 3.4291
+ "cost_for_plan": 3.4291,
+ "estimated_join_cardinality": 0.5849
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.b = 2 and t1.c = 3",
"attached_conditions_computation": [],
@@ -1522,14 +1653,25 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
"chosen": true,
"use_tmp_table": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 16,
+ "cost": 2.0312,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 16,
- "cost_for_plan": 5.2313
+ "cost_for_plan": 5.2313,
+ "estimated_join_cardinality": 16
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.a >= 20010104e0",
"attached_conditions_computation": [],
@@ -1702,14 +1844,25 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
"chosen": true,
"use_tmp_table": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 16,
+ "cost": 2.0312,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 16,
- "cost_for_plan": 5.2313
+ "cost_for_plan": 5.2313,
+ "estimated_join_cardinality": 16
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.a = 20010104e0",
"attached_conditions_computation": [],
@@ -1972,14 +2125,25 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"chosen": false,
"cause": "cost"
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "ref",
+ "records": 21,
+ "cost": 22,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 21,
- "cost_for_plan": 26.2
+ "cost_for_plan": 26.2,
+ "estimated_join_cardinality": 21
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.a = 1 and t1.b = 2",
"attached_conditions_computation": [],
@@ -2202,14 +2366,25 @@ select t1.a from t1 left join t2 on t1.a=t2.a {
"cost": 2.0068,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 4,
+ "cost": 2.0068,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 4,
- "cost_for_plan": 2.8068
+ "cost_for_plan": 2.8068,
+ "estimated_join_cardinality": 4
}
]
},
{
+ "best_join_order": ["t2", "t1"]
+ },
+ {
"condition_on_constant_tables": "1"
},
{
@@ -2318,7 +2493,14 @@ explain select * from t1 left join t2 on t2.a=t1.a {
"cost": 2.0068,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 4,
+ "cost": 2.0068,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 4,
"cost_for_plan": 2.8068,
@@ -2341,16 +2523,27 @@ explain select * from t1 left join t2 on t2.a=t1.a {
"cost": 8.0176,
"chosen": false
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "eq_ref",
+ "records": 1,
+ "cost": 4,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 4,
- "cost_for_plan": 7.6068
+ "cost_for_plan": 7.6068,
+ "estimated_join_cardinality": 4
}
]
}
]
},
{
+ "best_join_order": ["t1", "t2"]
+ },
+ {
"condition_on_constant_tables": "1"
},
{
@@ -2491,14 +2684,25 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
"cost": 2.0068,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 4,
+ "cost": 2.0068,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 4,
- "cost_for_plan": 2.8068
+ "cost_for_plan": 2.8068,
+ "estimated_join_cardinality": 4
}
]
},
{
+ "best_join_order": ["t3", "t2", "t1"]
+ },
+ {
"condition_on_constant_tables": "1"
},
{
@@ -2683,10 +2887,18 @@ explain extended select * from t1 where a in (select pk from t10) {
"cost": 2.022,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 10,
- "cost_for_plan": 4.022
+ "cost_for_plan": 4.022,
+ "estimated_join_cardinality": 10
}
]
}
@@ -2706,7 +2918,14 @@ explain extended select * from t1 where a in (select pk from t10) {
"cost": 2.0066,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0066,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6066,
@@ -2723,7 +2942,14 @@ explain extended select * from t1 where a in (select pk from t10) {
"cost": 2.022,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 30,
"cost_for_plan": 10.629,
@@ -2746,7 +2972,8 @@ explain extended select * from t1 where a in (select pk from t10) {
{
"chosen_strategy": "SJ-Materialize"
}
- ]
+ ],
+ "estimated_join_cardinality": 3
}
]
},
@@ -2761,7 +2988,14 @@ explain extended select * from t1 where a in (select pk from t10) {
"cost": 2.022,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 10,
"cost_for_plan": 4.022,
@@ -2783,6 +3017,9 @@ explain extended select * from t1 where a in (select pk from t10) {
]
},
{
+ "best_join_order": ["t1", "<subquery2>"]
+ },
+ {
"condition_on_constant_tables": "1"
},
{
@@ -3105,14 +3342,25 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"chosen": false,
"cause": "cost"
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "ref",
+ "records": 1,
+ "cost": 1.0043,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 1,
- "cost_for_plan": 1.2043
+ "cost_for_plan": 1.2043,
+ "estimated_join_cardinality": 1
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1",
"attached_conditions_computation": [],
@@ -3219,14 +3467,25 @@ select f1(a) from t1 {
"cost": 2.0068,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 4,
+ "cost": 2.0068,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 4,
- "cost_for_plan": 2.8068
+ "cost_for_plan": 2.8068,
+ "estimated_join_cardinality": 4
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -3307,14 +3566,25 @@ select f2(a) from t1 {
"cost": 2.0068,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 4,
+ "cost": 2.0068,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 4,
- "cost_for_plan": 2.8068
+ "cost_for_plan": 2.8068,
+ "estimated_join_cardinality": 4
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -3354,7 +3624,7 @@ a
2
select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
length(trace)
-1831
+2205
set optimizer_trace_max_mem_size=100;
select * from t1;
a
@@ -3368,7 +3638,7 @@ select * from t1 {
"join_preparation": {
"select_id": 1,
"steps": [
- 1731 0
+ 2105 0
set optimizer_trace_max_mem_size=0;
select * from t1;
a
@@ -3376,7 +3646,7 @@ a
2
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
-select * from t1 1831 0
+select * from t1 2205 0
drop table t1;
set optimizer_trace='enabled=off';
set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size;
@@ -3679,7 +3949,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"cost": 1.407,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "range",
+ "records": 3,
+ "cost": 1.407,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.007,
@@ -3703,10 +3980,18 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"chosen": false,
"cause": "cost"
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "ref",
+ "records": 1,
+ "cost": 3.007,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
- "cost_for_plan": 5.614
+ "cost_for_plan": 5.614,
+ "estimated_join_cardinality": 3
}
]
},
@@ -3721,7 +4006,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"cost": 1.407,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "range",
+ "records": 3,
+ "cost": 1.407,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.007,
@@ -3745,7 +4037,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"chosen": false,
"cause": "cost"
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "ref",
+ "records": 2,
+ "cost": 3.014,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 6,
"cost_for_plan": 6.2211,
@@ -3756,6 +4055,9 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
]
},
{
+ "best_join_order": ["t0", "t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.a = t0.a and t0.a < 3",
"attached_conditions_computation": [],
@@ -3875,14 +4177,25 @@ explain select * from (select rand() from t1)q {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
- "cost_for_plan": 2.6051
+ "cost_for_plan": 2.6051,
+ "estimated_join_cardinality": 3
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -3931,14 +4244,25 @@ explain select * from (select rand() from t1)q {
"cost": 3,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 3,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
- "cost_for_plan": 3.6
+ "cost_for_plan": 3.6,
+ "estimated_join_cardinality": 3
}
]
},
{
+ "best_join_order": ["<derived2>"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -4129,7 +4453,14 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -4145,10 +4476,18 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
- "cost_for_plan": 6.4103
+ "cost_for_plan": 6.4103,
+ "estimated_join_cardinality": 9
}
]
},
@@ -4163,7 +4502,14 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -4187,7 +4533,14 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -4204,7 +4557,14 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 6.4103,
@@ -4221,7 +4581,14 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 13.815,
@@ -4244,7 +4611,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
{
"chosen_strategy": "SJ-Materialize"
}
- ]
+ ],
+ "estimated_join_cardinality": 3
}
]
},
@@ -4259,7 +4627,14 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 6.4103,
@@ -4279,7 +4654,14 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -4297,7 +4679,14 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -4322,6 +4711,9 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
]
},
{
+ "best_join_order": ["t1", "<subquery2>"]
+ },
+ {
"condition_on_constant_tables": "1"
},
{
@@ -4596,7 +4988,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -4613,7 +5012,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 6.4103,
@@ -4630,7 +5036,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 24.626,
@@ -4661,7 +5074,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 44.641,
@@ -4683,7 +5103,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 62.846,
@@ -4706,7 +5133,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 729,
"cost_for_plan": 210.66,
@@ -4724,7 +5158,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
{
"chosen_strategy": "DuplicateWeedout"
}
- ]
+ ],
+ "estimated_join_cardinality": 27
}
]
},
@@ -4744,7 +5179,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 243,
"cost_for_plan": 95.256,
@@ -4764,7 +5206,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 41.031,
@@ -4786,7 +5235,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 59.246,
@@ -4809,7 +5265,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 729,
"cost_for_plan": 207.06,
@@ -4822,7 +5285,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
{
"chosen_strategy": "DuplicateWeedout"
}
- ]
+ ],
+ "estimated_join_cardinality": 27
}
]
},
@@ -4842,7 +5306,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 59.246,
@@ -4862,7 +5333,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 44.641,
@@ -4882,7 +5360,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 24.626,
@@ -4899,7 +5384,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 729,
"cost_for_plan": 172.44,
@@ -4930,7 +5422,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 304.05,
@@ -4953,7 +5452,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 729,
"cost_for_plan": 451.86,
@@ -4987,7 +5493,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 243,
"cost_for_plan": 336.46,
@@ -5007,7 +5520,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 243,
"cost_for_plan": 75.231,
@@ -5029,7 +5549,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 2187,
"cost_for_plan": 514.65,
@@ -5052,7 +5579,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 2187,
"cost_for_plan": 514.65,
@@ -5072,7 +5606,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 729,
"cost_for_plan": 172.44,
@@ -5094,7 +5635,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 2187,
"cost_for_plan": 611.85,
@@ -5126,7 +5674,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 6561,
"cost_for_plan": 1486.7,
@@ -5148,7 +5703,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 13.815,
@@ -5165,7 +5727,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 243,
"cost_for_plan": 64.431,
@@ -5187,7 +5756,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 2187,
"cost_for_plan": 503.85,
@@ -5210,7 +5786,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 2187,
"cost_for_plan": 503.85,
@@ -5230,7 +5813,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 243,
"cost_for_plan": 64.431,
@@ -5248,7 +5838,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 243,
"cost_for_plan": 64.431,
@@ -5268,7 +5865,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 24.626,
@@ -5285,7 +5889,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 729,
"cost_for_plan": 172.44,
@@ -5307,7 +5918,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 2187,
"cost_for_plan": 611.85,
@@ -5330,7 +5948,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 6561,
"cost_for_plan": 1486.7,
@@ -5350,7 +5975,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 243,
"cost_for_plan": 75.231,
@@ -5372,7 +6004,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 2187,
"cost_for_plan": 514.65,
@@ -5395,7 +6034,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 2187,
"cost_for_plan": 514.65,
@@ -5415,7 +6061,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 729,
"cost_for_plan": 172.44,
@@ -5437,7 +6090,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 10.021,
@@ -5455,7 +6115,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 10.021,
@@ -5473,7 +6140,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 6.4103,
@@ -5491,7 +6165,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 10.021,
@@ -5511,7 +6192,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -5529,7 +6217,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 3.8154,
@@ -5547,7 +6242,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 3.8154,
@@ -5565,7 +6267,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -5583,7 +6292,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 3.8154,
@@ -5596,6 +6312,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"fix_semijoin_strategies_for_picked_join_order": []
},
{
+ "best_join_order": [
+ "t_outer_1",
+ "t_inner_1",
+ "t_inner_2",
+ "t_inner_4",
+ "t_outer_2",
+ "t_inner_3"
+ ]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a",
"attached_conditions_computation": [],
@@ -5874,7 +6600,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -5890,10 +6623,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
- "cost_for_plan": 10.021
+ "cost_for_plan": 10.021,
+ "estimated_join_cardinality": 27
}
]
},
@@ -5908,7 +6649,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 3.8154,
@@ -5929,7 +6677,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -5945,10 +6700,18 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
- "cost_for_plan": 10.021
+ "cost_for_plan": 10.021,
+ "estimated_join_cardinality": 27
}
]
},
@@ -5963,7 +6726,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 3.8154,
@@ -5987,7 +6757,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -6004,7 +6781,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 6.4103,
@@ -6021,7 +6805,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 24.626,
@@ -6057,7 +6848,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 15.541,
@@ -6079,7 +6877,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 33.746,
@@ -6102,7 +6907,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 729,
"cost_for_plan": 181.56,
@@ -6125,7 +6937,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
{
"chosen_strategy": "SJ-Materialize"
}
- ]
+ ],
+ "estimated_join_cardinality": 27
}
]
},
@@ -6145,7 +6958,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 243,
"cost_for_plan": 66.156,
@@ -6165,7 +6985,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 11.931,
@@ -6187,7 +7014,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 30.146,
@@ -6210,7 +7044,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 30.146,
@@ -6230,7 +7071,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 15.541,
@@ -6250,7 +7098,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 24.626,
@@ -6268,7 +7123,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 13.815,
@@ -6286,7 +7148,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 81,
"cost_for_plan": 24.626,
@@ -6306,7 +7175,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 10.021,
@@ -6324,7 +7200,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 10.021,
@@ -6342,7 +7225,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 6.4103,
@@ -6360,7 +7250,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": true,
+ "filter_used": false
+ }
},
"rows_for_plan": 27,
"cost_for_plan": 10.021,
@@ -6380,7 +7277,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -6398,7 +7302,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 3.8154,
@@ -6416,7 +7327,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 3.8154,
@@ -6434,7 +7352,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
"cost_for_plan": 2.6051,
@@ -6452,7 +7377,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"cost": 2.0154,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 9,
+ "cost": 2.0154,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 9,
"cost_for_plan": 3.8154,
@@ -6488,6 +7420,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
]
},
{
+ "best_join_order": [
+ "t_outer_1",
+ "<subquery2>",
+ "t_outer_2",
+ "<subquery3>"
+ ]
+ },
+ {
"condition_on_constant_tables": "1"
},
{
@@ -7130,7 +8070,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 2.8296,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method":
+ {
+ "type": "scan",
+ "records": 5.9375,
+ "cost": 2.8296,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 5.9375,
"cost_for_plan": 4.0171,
@@ -7154,10 +8102,19 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 256.85,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method":
+ {
+ "type": "scan",
+ "records": 804.69,
+ "cost": 256.85,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 4777.8,
- "cost_for_plan": 1216.4
+ "cost_for_plan": 1216.4,
+ "estimated_join_cardinality": 4777.8
}
]
},
@@ -7178,7 +8135,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 43.26,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method":
+ {
+ "type": "scan",
+ "records": 804.69,
+ "cost": 43.26,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 804.69,
"cost_for_plan": 204.2,
@@ -7214,7 +8179,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 2.0171,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method":
+ {
+ "type": "scan",
+ "records": 10,
+ "cost": 2.0171,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 10,
"cost_for_plan": 4.0171,
@@ -7248,11 +8221,20 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 43.26,
"chosen": false
}
- ]
+ ],
+ "chosen_access_method":
+ {
+ "type": "ref",
+ "records": 1,
+ "cost": 20,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 10,
"cost_for_plan": 26.017,
- "selectivity": 0.8047
+ "selectivity": 0.8047,
+ "estimated_join_cardinality": 8.0469
}
]
},
@@ -7273,7 +8255,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 43.26,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method":
+ {
+ "type": "scan",
+ "records": 804.69,
+ "cost": 43.26,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 804.69,
"cost_for_plan": 204.2,
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index 5697e3a771a..7f1bd5163f3 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -207,19 +207,30 @@ explain select * from t1 where a=1 or b=1 {
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "range",
+ "access_type": "index_merge",
"resulting_rows": 2,
"cost": 4.1484,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "index_merge",
+ "records": 2,
+ "cost": 4.1484,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 2,
- "cost_for_plan": 4.5484
+ "cost_for_plan": 4.5484,
+ "estimated_join_cardinality": 2
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.a = 1 or t1.b = 1",
"attached_conditions_computation": [],
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 23a500b0720..afcf0b03dae 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -208,14 +208,25 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
"chosen": false,
"cause": "cost"
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "ref",
+ "records": 1,
+ "cost": 2,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 1,
- "cost_for_plan": 2.2
+ "cost_for_plan": 2.2,
+ "estimated_join_cardinality": 1
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": "t1.key1 = 1 and t1.pk1 <> 0",
"attached_conditions_computation": [],
diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result
index c8112fd5f6c..22c5f3ff6f6 100644
--- a/mysql-test/main/opt_trace_security.result
+++ b/mysql-test/main/opt_trace_security.result
@@ -98,14 +98,25 @@ select * from db1.t1 {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
- "cost_for_plan": 2.6051
+ "cost_for_plan": 2.6051,
+ "estimated_join_cardinality": 3
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
@@ -211,14 +222,25 @@ select * from db1.v1 {
"cost": 2.0051,
"chosen": true
}
- ]
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records": 3,
+ "cost": 2.0051,
+ "uses_join_buffering": false,
+ "filter_used": false
+ }
},
"rows_for_plan": 3,
- "cost_for_plan": 2.6051
+ "cost_for_plan": 2.6051,
+ "estimated_join_cardinality": 3
}
]
},
{
+ "best_join_order": ["t1"]
+ },
+ {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc
index befc7934a3a..c80037ba864 100644
--- a/sql/opt_trace.cc
+++ b/sql/opt_trace.cc
@@ -630,6 +630,45 @@ void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab)
table_rec.add("rows", tab->found_records)
.add("cost", tab->read_time);
}
+
+/*
+ Print the join order of all the tables for top level select.
+
+ For example:
+
+ select * from ot1
+ where ot1.a IN (select it1.a from it1, it2 where it1.b=it2.a);
+
+ So this function would print
+ ot1, <subquery2> ----> For select #1
+*/
+
+void print_final_join_order(JOIN *join)
+{
+ Json_writer_object join_order(join->thd);
+ Json_writer_array best_order(join->thd, "best_join_order");
+ JOIN_TAB *j;
+ uint i;
+ for (j= join->join_tab,i=0 ; i < join->top_join_tab_count;
+ i++, j++)
+ best_order.add_table_name(j);
+}
+
+
+void print_best_access_for_table(THD *thd, POSITION *pos,
+ enum join_type type)
+{
+ Json_writer_object trace_best_access(thd, "chosen_access_method");
+ trace_best_access.add("type", type == JT_ALL ? "scan" :
+ join_type_str[type]);
+ trace_best_access.add("records", pos->records_read);
+ trace_best_access.add("cost", pos->read_time);
+ trace_best_access.add("uses_join_buffering", pos->use_join_buffer);
+ trace_best_access.add("filter_used",
+ pos->range_rowid_filter_info != NULL);
+}
+
+
/*
Introduce enum_query_type flags parameter, maybe also allow
EXPLAIN also use this function.
diff --git a/sql/opt_trace.h b/sql/opt_trace.h
index 52318bc6b7f..6fe179d79d8 100644
--- a/sql/opt_trace.h
+++ b/sql/opt_trace.h
@@ -105,6 +105,9 @@ void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex,
Json_writer_object *trace_object);
void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab);
+void print_final_join_order(JOIN *join);
+void print_best_access_for_table(THD *thd, POSITION *pos,
+ enum join_type type);
/*
Security related (need to add a proper comment here)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bd6d9852f40..f6435af4ccb 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7203,6 +7203,7 @@ best_access_path(JOIN *join,
SplM_plan_info *spl_plan= 0;
Range_rowid_filter_cost_info *filter= 0;
const char* cause= NULL;
+ enum join_type best_type= JT_UNKNOWN, type= JT_UNKNOWN;
disable_jbuf= disable_jbuf || idx == join->const_tables;
@@ -7342,7 +7343,8 @@ best_access_path(JOIN *join,
*/
tmp= prev_record_reads(join->positions, idx, found_ref);
records= 1.0;
- trace_access_idx.add("access_type", "fulltext")
+ type= JT_FT;
+ trace_access_idx.add("access_type", join_type_str[type])
.add("index", keyinfo->name);
}
else
@@ -7365,14 +7367,16 @@ best_access_path(JOIN *join,
(!(key_flags & HA_NULL_PART_KEY) || // (2)
all_key_parts == notnull_part)) // (3)
{
- trace_access_idx.add("access_type", "eq_ref")
+ type= JT_EQ_REF;
+ trace_access_idx.add("access_type", join_type_str[type])
.add("index", keyinfo->name);
tmp = prev_record_reads(join->positions, idx, found_ref);
records=1.0;
}
else
{
- trace_access_idx.add("access_type", "ref")
+ type= JT_REF;
+ trace_access_idx.add("access_type", join_type_str[type])
.add("index", keyinfo->name);
if (!found_ref)
{ /* We found a const key */
@@ -7467,8 +7471,8 @@ best_access_path(JOIN *join,
}
else
{
- trace_access_idx.add("access_type",
- ref_or_null_part ? "ref_or_null" : "ref")
+ type = ref_or_null_part ? JT_REF_OR_NULL : JT_REF;
+ trace_access_idx.add("access_type", join_type_str[type])
.add("index", keyinfo->name);
/*
Use as much key-parts as possible and a uniq key is better
@@ -7683,6 +7687,7 @@ best_access_path(JOIN *join,
best_max_key_part= max_key_part;
best_ref_depends_map= found_ref;
best_filter= filter;
+ best_type= type;
}
else
{
@@ -7736,6 +7741,7 @@ best_access_path(JOIN *join,
best_ref_depends_map= 0;
best_uses_jbuf= TRUE;
best_filter= 0;
+ best_type= JT_HASH;
trace_access_hash.add("type", "hash");
trace_access_hash.add("index", "hj-key");
trace_access_hash.add("cost", rnd_records);
@@ -7799,10 +7805,6 @@ best_access_path(JOIN *join,
filter= 0;
if (s->quick)
{
- trace_access_scan.add("access_type", "range");
- /*
- should have some info about all the different QUICK_SELECT
- */
/*
For each record we:
- read record range through 'quick'
@@ -7828,23 +7830,29 @@ best_access_path(JOIN *join,
{
tmp-= filter->get_adjusted_gain(rows);
DBUG_ASSERT(tmp >= 0);
- }
+ }
+ type= JT_RANGE;
}
else
{
+ type= JT_INDEX_MERGE;
best_filter= 0;
}
-
loose_scan_opt.check_range_access(join, idx, s->quick);
}
else
{
- trace_access_scan.add("access_type", "scan");
/* Estimate cost of reading table. */
if (s->table->force_index && !best_key) // index scan
+ {
+ type= JT_NEXT;
tmp= s->table->file->read_time(s->ref.key, 1, s->records);
+ }
else // table scan
+ {
tmp= s->scan_time();
+ type= JT_ALL;
+ }
if ((s->table->map & join->outer_join) || disable_jbuf) // Can't use join cache
{
@@ -7874,6 +7882,9 @@ best_access_path(JOIN *join,
}
}
+ trace_access_scan.add("access_type", type == JT_ALL ?
+ "scan" :
+ join_type_str[type]);
/* Splitting technique cannot be used with join cache */
if (s->table->is_splittable())
tmp+= s->table->get_materialization_cost();
@@ -7913,6 +7924,7 @@ best_access_path(JOIN *join,
best_uses_jbuf= MY_TEST(!disable_jbuf && !((s->table->map &
join->outer_join)));
spl_plan= 0;
+ best_type= type;
}
trace_access_scan.add("chosen", best_key == NULL);
}
@@ -7944,6 +7956,11 @@ best_access_path(JOIN *join,
trace_access_scan.add("use_tmp_table", true);
join->sort_by_table= (TABLE*) 1; // Must use temporary table
}
+ trace_access_scan.end();
+ trace_paths.end();
+
+ if (unlikely(thd->trace_started()))
+ print_best_access_for_table(thd, pos, best_type);
DBUG_VOID_RETURN;
}
@@ -9484,6 +9501,8 @@ best_extension_by_limited_search(JOIN *join,
Hence it may be wrong.
*/
current_read_time= COST_ADD(current_read_time, current_record_count);
+ trace_one_table.add("estimated_join_cardinality",
+ partial_join_cardinality);
if (current_read_time < join->best_read)
{
memcpy((uchar*) join->best_positions, (uchar*) join->positions,
@@ -10305,6 +10324,9 @@ bool JOIN::get_best_combination()
top_join_tab_count= (uint)(join_tab_ranges.head()->end -
join_tab_ranges.head()->start);
+ if (unlikely(thd->trace_started()))
+ print_final_join_order(this);
+
update_depend_map(this);
DBUG_RETURN(0);
}
View Lorry Controller Status