How to use hints in complex SQL to specify a join operator as index_join

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 复杂sql如何hint,指定某一个连接算子为index_join

| username: BraveChen

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.3.2

[Issue Phenomenon and Impact]
In a complex SQL query, it was confirmed that the bottleneck of the query time lies in a certain left join table, which performs a full table scan of 10 million rows each time (without filter conditions), resulting in high time consumption. However, the join condition of this table has an index. It was also confirmed that another sub-joined table also has no filter conditions (4 million rows), and it behaves similarly. But in the execution plan, it uses the index as the probe side of the index join.

IKgqX5L8ky
IKgqX5L8ky1244×624 160 KB

image
image789×338 20.2 KB

[Attempts Made]
image No effect

2
| username: tidb狂热爱好者 | Original post link

You need to enable clustered index.

3
| username: BraveChen | Original post link

Are you referring to creating a composite index on the two join conditions of the wti table? It has already been created.

4
| username: 小龙虾爱大龙虾 | Original post link

Could you please provide the operator info after the hash join?

5
| username: BraveChen | Original post link

left outer join, equal: [eq(k3_wms.wm_pick_distribute_item.to_item_id, k3_wms.wm_to_item.id) eq(Column#703, Column#704)]

6
| username: 小龙虾爱大龙虾 | Original post link

Check the type of to_id in both tables. It would be helpful if you could provide the SQL text and execution plan text.

7
| username: forever | Original post link

You can try adjusting the order of the composite index; different orders may generate different execution plans.

8
| username: BraveChen | Original post link

Confirmed that there is a mismatch in the field types of a join condition. :rofl: I directly removed the mismatched join condition, and the execution plan automatically identified and optimized this part.

9
| username: BraveChen | Original post link

I’ve seen this situation elsewhere :rofl:

10
| username: forever | Original post link

I have optimized this type of SQL on MySQL. The join order of tables will be different with different index orders. Initially, I used hit to test and it executed faster. Then, changing the index order directly generated the same execution plan as hit. :smile:

11
| username: BraveChen | Original post link

:rofl::rofl::rofl::see_no_evil::see_no_evil:

12
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.