# # 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;