Changing the Execution Order of WHERE Clauses Using Hints

Translated
1

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

Original topic: 通过Hint改变where语句执行顺序

| username: Hacker_4BgeX58z

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.4.3
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Problem Phenomenon and Impact]
SELECT *
FROM table_a a LEFT JOIN table_b b ON a.user_id = b.user_id
WHERE a.a = ‘1’
AND b.b = ‘1’;

As shown in the SQL above, examining the execution plan of the above statement at different times yields two different results:
First result: Prioritizes executing the WHERE clause a.a=‘1’, execution speed is very fast;
Second result: Prioritizes executing the WHERE clause b.b=‘1’, execution speed is very slow;
Can we use a Hint to specify that the SQL statement should prioritize executing the WHERE clause a.a=‘1’ during execution?

[Resource Configuration] Navigate to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

2
| username: h5n1 | Original post link

Post the execution plans for the two different methods.

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

The left join should first execute a.a = ‘1’, right?

4
| username: 春风十里 | Original post link

I see that only STRAIGHT_JOIN can control the table join order in an equi-join. For your left join, I didn’t see a method to control the execution order. However, if the optimizer doesn’t follow the fastest path, you can consider using ANALYZE or adding indexes to try to make the optimizer choose the faster path automatically.

STRAIGHT_JOIN()

The STRAIGHT_JOIN() hint instructs the optimizer to join tables in the order they appear in the FROM clause.

SELECT /*+ STRAIGHT_JOIN() */ * FROM t t1, t t2 WHERE t1.a = t2.a;
5
| username: system | Original post link

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