Is there any solution other than placing it in a subquery when the condition is slow due to one being null?

Translated
1

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

Original topic: 判断条件有一个为空就会很慢，除了放在子查询有其他解决方案吗？

| username: Jjjjayson_zeng

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[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]

image
image1368×826 41.1 KB

Adding or not adding this line makes a huge performance difference
case.sql (4.7 KB)

Execution Plan without Empty Line.xlsx (12.8 KB)
Execution Plan with Empty Line.xlsx (13.6 KB)

2
| username: zhanggame1 | Original post link

Try manually analyzing the involved tables and then give it another shot.

3
| username: xfworld | Original post link

First, use WITH to create a set, and then filtering will be faster.

4
| username: come_true | Original post link

Try collecting statistics.

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

If you don’t make a judgment, is the atdpersontimesheet table not even in the execution plan?

6
| username: andone | Original post link

Take a look at the specific execution plan.

7
| username: Miracle | Original post link

It seems that not only a WHERE condition was deleted, but the table is also gone. Did you get the wrong execution plan?

8
| username: zhanggame1 | Original post link

Check again if the SQL was sent incorrectly.

9
| username: Kongdom | Original post link

:thinking: Can we replace the left join with an inner join in the sheet table here?

10
| username: dba远航 | Original post link

That condition directly affects the use of the index.

11
| username: system | Original post link

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