Hi, I am using MySQL 8.3.0 to test the impact of different join orders. I use the JOB (Join Order Benchmark, https://github.com/gregrahn/join-order-benchmark ) in my experiment. Now I want to use the optimizer hint to force the join orders (including both the table order and join tree shape) for a SQL query. For example, the Q3b query of the JOB benchmark is as follows:
SELECT MIN(t.title) AS movie_title
FROM keyword AS k,
movie_info AS mi,
movie_keyword AS mk,
title AS t
WHERE k.keyword LIKE '%sequel%'
AND mi.info IN ('Bulgaria')
AND t.production_year > 2010
AND t.id = mi.movie_id
AND t.id = mk.movie_id
AND mk.movie_id = mi.movie_id
AND k.id = mk.keyword_id;
The default execution plan in MySQL 8.3.0 (with only primary key indexes) is a zig-zag tree shape with join order as follows
(mi ((mk k) t)).
Based on the documentation ( https://dev.mysql.com/doc/refman/8.3/en/optimizer-hints.html#optimizer-hints-join-order ), I could change the join order by using the JOIN_ORDER hint like:
/*+ JOIN_ORDER(k, mk, mi, t) */
or
/*+ JOIN_ORDER(t, mi, mk, k) */
I test the above two join order hints and get the query plans with the following join orders:
((mi (mk k)) t)
and
((mk (mi t)) k).
We could find that the two hints have successfully changed the table order. My question is how to specify or control join tree shapes with an optimizer hint. For example, how to force a left-deep tree like
(((k, mk), mi), t)
or a bushy tree like
((k, mk), (t, mi)).
I tried the following two hints respectively in MySQL 8.3.0:
/*+ JOIN_ORDER(((k, mk), mi), t) */
/*+ JOIN_ORDER((k, mk), (t, mi)) */
But failed, I found they could not work to control the join tree shapes. Thanks!