The issue of LEFT JOIN not using indexes

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】

| 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.

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

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

| username: Hacker_0LElpqkb | Original post link

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

| 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.

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

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

| username: 江湖故人 | Original post link

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

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

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

| 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.