The issue of LEFT JOIN not using indexes

Translated
1

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

Original topic: LEFT JOIN 不走索引的问题

| username: Hacker_0LElpqkb

【TiDB Usage Environment】Production Environment
【TiDB Version】7.5.0
【Reproduction Path】Operations performed that led to the issue
【Encountered Issue: Problem Phenomenon and Impact】
The kid in table A is the primary key, and the kid in table B is a normal index. During the join query, the index of table A cannot be hit. Both tables A and B have around 13 million records.
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

7e2598358dcbd2f7500a1d578b3a616
7e2598358dcbd2f7500a1d578b3a6161527×647 13.6 KB

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

Too much is hidden, can’t see anything. Check the data types of the associated columns and the table’s character set collation.

3
| username: tidb菜鸟一只 | Original post link

Try adding the hint /*+ INL_JOIN(fjxx, info) */, but the efficiency should be worse than your current execution plan.

4
| username: Hacker_0LElpqkb | Original post link

I tried it, and the execution plan is the same.

5
| username: Hacker_0LElpqkb | Original post link

Okay, I checked the collation of the fields and found they were inconsistent. After making them consistent, it worked. Thank you, thank you.

6
| username: 麻烦是朋友 | Original post link

Check if the character sets and field types of the two tables are the same.

7
| username: 江湖故人 | Original post link

Review :nerd_face:
Analysis of Situations Where MySQL Does Not Use Indexes - Tencent Cloud Developer Community - Tencent Cloud (tencent.com)

8
| username: 这里介绍不了我 | Original post link

If there is no index, create an index; if there is an index, check the character set and collation.

9
| username: dba远航 | Original post link

Yes, the character set of the fields must be the same, and the collation must be the same, so that it is possible to use index association.