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

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]

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)

| username: zhanggame1 | Original post link

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

| username: xfworld | Original post link

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

| username: come_true | Original post link

Try collecting statistics.

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

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

| username: andone | Original post link

Take a look at the specific execution plan.

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

| username: zhanggame1 | Original post link

Check again if the SQL was sent incorrectly.

| username: Kongdom | Original post link

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

| username: dba远航 | Original post link

That condition directly affects the use of the index.

| username: system | Original post link

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