Debian Codegraph @

mysql-test/suite/opt_trace/r/join_order.result

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