Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
This task aims to print the semi-join strategy consideration for the join order inside the optimizer trace.
Mysql has this already.
An example would be:
Dataset
create table t0(a int); |
insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
create table t1 (a int, b int, c int); |
insert t1 select A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100 from t0 A, t0 B, t0 C; |
create table t2 (a int, b int, c int); |
insert t2 select a,a,a from t0; |
create table t3 (a int, b int, c int, key(a)); |
insert t3 select a,a,a from t0; |
Query is
select * from t1, t2 where t1.a=t2.a and |
t1.b in (select b from t3 where t3.c < 3) order by t1.b,t2.b desc limit 5; |
So the trace has output like this, where we consider all the semi-join strategies
"semijoin_strategy_choice": [
|
{
|
"strategy": "MaterializeScan",
|
"recalculate_access_paths_and_cost": {
|
"tables": [
|
{
|
"table": "`t1`",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"rows_to_scan": 1000,
|
"access_type": "scan",
|
"using_join_cache": true,
|
"buffers_needed": 1,
|
"resulting_rows": 1000,
|
"cost": 6670,
|
"chosen": true
|
}
|
]
|
}
|
}
|
]
|
},
|
"cost": 6685.3,
|
"rows": 100,
|
"duplicate_tables_left": true,
|
"chosen": true
|
},
|
{
|
"strategy": "DuplicatesWeedout",
|
"cost": 6770.7,
|
"rows": 100,
|
"duplicate_tables_left": false,
|
"chosen": false
|
}
|
],
|