1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
#
# Bug#34299473: Hypergraph : More join order information in the optimizer
# trace
#
SET optimizer_trace="enabled=on,one_line=off";
create table json_tab(je json);
create table t1(id int);
insert into t1 values (1), (2), (3);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
select * from t1, t1 t2, t1 t3 where t1.id = t2.id and t2.id = t3.id;
id id id
1 1 1
2 2 2
3 3 3
insert into json_tab
select json_extract(trace,"$.steps[*].join_optimization.steps[*].join_optimizer[*]") je
from information_schema.optimizer_trace;
select jt.t from
json_tab, json_table(json_tab.je, "$[*]" COLUMNS(t text path "$")) as jt
where jt.t like '%current access paths%join_order%' order by t;
t
- current access paths for {t1,t2,t3}: {HASH_JOIN, cost=1.8, init_cost=1.3, rescan_cost=0.6, rows=0.3, join_order=(t1,(t2,t3))}, {NESTED_LOOP_JOIN, cost=2.5, init_cost=0.0, rows=0.3, join_order=((t2,t3),t1)}, {NESTED_LOOP_JOIN, cost=1.7, init_cost=0.6, rows=0.3, join_order=((t2,t3),t1)})
- current access paths for {t1,t2,t3}: {HASH_JOIN, cost=1.8, init_cost=1.3, rescan_cost=0.6, rows=0.3, join_order=(t1,(t2,t3))}, {NESTED_LOOP_JOIN, cost=2.5, init_cost=0.0, rows=0.3, join_order=((t2,t3),t1)}, {NESTED_LOOP_JOIN, cost=1.7, init_cost=0.6, rows=0.3, join_order=((t2,t3),t1)})
- current access paths for {t1,t2,t3}: {HASH_JOIN, cost=1.8, init_cost=1.3, rescan_cost=0.6, rows=0.3, join_order=(t1,(t2,t3))}, {NESTED_LOOP_JOIN, cost=2.5, init_cost=0.0, rows=0.3, join_order=((t2,t3),t1)}, {NESTED_LOOP_JOIN, cost=1.7, init_cost=0.6, rows=0.3, join_order=((t2,t3),t1)})
- current access paths for {t1,t2,t3}: {HASH_JOIN, cost=1.8, init_cost=1.3, rescan_cost=0.6, rows=0.3, join_order=(t3,(t1,t2))}, {NESTED_LOOP_JOIN, cost=2.5, init_cost=0.0, rows=0.3, join_order=((t2,t3),t1)}, {NESTED_LOOP_JOIN, cost=1.7, init_cost=0.6, rows=0.3, join_order=((t2,t3),t1)})
- current access paths for {t1,t2,t3}: {HASH_JOIN, cost=1.8, init_cost=1.3, rescan_cost=0.6, rows=0.3, join_order=(t3,(t1,t2))}, {NESTED_LOOP_JOIN, cost=2.5, init_cost=0.0, rows=0.3, join_order=((t2,t3),t1)}, {NESTED_LOOP_JOIN, cost=1.7, init_cost=0.6, rows=0.3, join_order=((t2,t3),t1)})
- current access paths for {t1,t2,t3}: {HASH_JOIN, cost=1.8, init_cost=1.3, rescan_cost=0.6, rows=0.3, join_order=(t3,(t1,t2))}, {NESTED_LOOP_JOIN, cost=2.5, init_cost=0.0, rows=0.3, join_order=((t2,t3),t1)}, {NESTED_LOOP_JOIN, cost=1.7, init_cost=0.6, rows=0.3, join_order=((t2,t3),t1)})
- current access paths for {t1,t2}: {HASH_JOIN, cost=1.2, init_cost=0.6, rescan_cost=0.6, rows=0.9, join_order=(t1,t2)}, {NESTED_LOOP_JOIN, cost=1.9, init_cost=0.0, rows=0.9, join_order=(t1,t2)})
- current access paths for {t1,t3}: {HASH_JOIN, cost=1.2, init_cost=0.6, rescan_cost=0.6, rows=0.9, join_order=(t1,t3)}, {NESTED_LOOP_JOIN, cost=1.9, init_cost=0.0, rows=0.9, join_order=(t1,t3)})
- current access paths for {t2,t3}: {HASH_JOIN, cost=1.2, init_cost=0.6, rescan_cost=0.6, rows=0.9, join_order=(t2,t3)}, {NESTED_LOOP_JOIN, cost=1.9, init_cost=0.0, rows=0.9, join_order=(t2,t3)})
drop table t1;
truncate table json_tab;
create table t1 (a int primary key, b int);
insert into t1 values (1,1);
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
with x1 as (select max(a) as m1 from t1 group by b)
select * from t1 y1 left join t1 y2 on y1.a=-y2.a
where y1.b+y2.b = (select max(m1) from x1);
a b a b
insert into json_tab
select json_extract(trace,"$.steps[*].join_optimization.steps[*].join_optimizer[*]") je
from information_schema.optimizer_trace;
select jt.t from
json_tab, json_table(json_tab.je, "$[*]" COLUMNS(t text path "$")) as jt
where jt.t like '%current access paths%join_order%' order by t;
t
- current access paths for {y1,y2}: {HASH_JOIN, cost=0.7, init_cost=0.3, rescan_cost=0.4, rows=0.1, join_order=(y1,y2)}, {NESTED_LOOP_JOIN, cost=3.7, init_cost=0.0, rows=0.1, join_order=(y2,y1)})
drop table t1;
drop table json_tab;
|
