Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: Hint中表连接顺序LEADING应支持多表关联优先级和兼容物理优化阶段Join算法的选择
[Problem Scenario Involved in the Requirement] Inconvenience in using leading during multi-table joins and lack of support for choosing join algorithms during the physical optimization phase.
[Expected Requirement Behavior] Support leading multi-table priority and compatibility with choosing join algorithms (i.e., MERGE_JOIN
, INL_JOIN
, INL_HASH_JOIN
, HASH_JOIN
).
- Hope leading supports multi-table association priority
Currently, TiDB’s join reorder capability still needs further enhancement. We often encounter situations where we need to manually specify the join order to optimize statements. For example:
These are due to the insufficient join reorder algorithm, which did not choose a better join order. Therefore, we need to manually specify the order. However, when using leading, we found that the leading function does not support priority. For example:
select * from a, b, c where a.id = b.id and a.id = c.id
The join orders of the tables are:
(a, b), c
c, (a, b)
(a, c), b
b, (a, c)
But the leading syntax does not support priority selection. Therefore, can the leading be enhanced to support the following forms:
leading(a, (b, c))
leading((a, b), (c, d))
Can leading support multi-table association priority?
- Hope leading is compatible with choosing join algorithms (i.e.,
MERGE_JOIN
,INL_JOIN
,INL_HASH_JOIN
,HASH_JOIN
)
Since leading occurs in the logical optimization phase, I understand that this does not conflict with physical optimization (choice of join algorithm). Why has it not been compatible with the choice of join algorithms?