TiDB Left Join Query is Very Slow

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

Original topic: tidb left join 查询很慢

| username: 奋斗的大象

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed that caused the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots / Logs / Monitoring]

| username: Jasper | Original post link

First, this CPU time is not the actual elapsed time; it just represents the CPU usage. Second, if you need to see the specific execution details of the SQL, you should check the execution plan of this SQL in the slow query log.

| username: 奋斗的大象 | Original post link

The specific execution plan of SQL

Only 340,000 rows of data, the execution result is [Err] 2013 - Lost connection to xxserver

| username: andone | Original post link

Please post the specific SQL execution plan. Is this the SQL you executed?

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

All I can say is, wow, that’s some impressive SQL writing, “select distinct *”, “join between and” :joy_cat:

| username: 像风一样的男子 | Original post link

This SQL is written very well, but it needs optimization…
It seems that this logic doesn’t need to use a left join at all.

| username: zhanggame1 | Original post link

SQL can be rewritten, there’s no need for both tables t1 and t2, just join them directly.

| username: TIDB-Learner | Original post link

How is the data volume of the two tables? Use the small table to drive the large table.

| username: TIDB-Learner | Original post link

  1. <> does not use an index. 2. Would ifnull be better? 3. Judging by your script, the data volume of t2 should be smaller.
| username: forever | Original post link

This is definitely a Cartesian product. Every piece of data in t1 has to check the entire t2 table to see how many rows meet the conditions.

| username: Kongdom | Original post link

:thinking: According to your WHERE condition, you can completely replace it with an INNER JOIN.

| username: TIDB-Learner | Original post link

For small tables, perform a full table scan and then use the results to look up the large table. Using tree search, the overall scan won’t take much longer.

| username: forever | Original post link

Although you are using a left join, if all entries in table T2 can be matched, then if table T1 has 100 rows, the result set will be T2 * 100 rows of data.