Optimization of OR Query Subqueries

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

Original topic: OR查询 子查询优化

| username: TiDBer_s7AnS8qP

[TiDB Usage Environment] All environments
[TiDB Version]
[Reproduction Path] After adding ‘or’, the subquery becomes slower, and the execution plan changes. Is there any way to optimize ‘or’ without merging the two ‘or’ conditions, since ‘or’ is configured based on role permissions and cannot be changed?
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]






Additionally, adding one condition to the query makes it extremely slow, even though it only adds two thousand more rows. Why does it slow down so much?

| username: zhanggame1 | Original post link

How about splitting it into two SQL queries and then using UNION?

| username: come_true | Original post link

What tool is this? The interface is so beautiful.

| username: 随缘天空 | Original post link

The OR connection might not use the index; try using the UNION connection instead. Although the query result has 2,000 more rows, the search range might have increased by tens of millions of rows. You can’t judge solely based on the search results.

| username: xfworld | Original post link

It looks a lot like an idea.

| username: come_true | Original post link

Is it free?

| username: xfworld | Original post link

Definitely not free :stuck_out_tongue:

| username: xfworld | Original post link

Try to optimize the indexes on the potential_cust_org table. Ensuring that queries use indexes as much as possible and avoid table scans will significantly improve performance.

Remove any unnecessary conditions like 1 = 1, as they are not needed.

| username: forever | Original post link

To prevent errors, this type of concatenated statement usually includes “1=1”, otherwise, the statement without the concatenated condition will result in an error.

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

Instead of using “or,” you can just continue with “union” in the subquery above.

| username: Kongdom | Original post link

:joy: When dynamically concatenating SQL in a program, it’s often written like this. Can’t get rid of it, simply can’t get rid of it~

| username: 随缘天空 | Original post link

Using the WHERE clause should be able to remove it.

| username: zhanggame1 | Original post link

Nowadays, everyone uses ORM, so we don’t do this anymore.

| username: Kongdom | Original post link

I’m a developer, to be honest, it’s hard to remove. :joy:

| username: Kongdom | Original post link

:wink: According to what you said, we are quite traditional, hehehe.

| username: dba远航 | Original post link

The OR condition for the same field can be converted to IN, and the OR condition for different fields can be replaced with UNION ALL. This is also the logic of code-level conversion.

| username: TiDBer_小阿飞 | Original post link

Community Edition is free, Ultimate Edition is free for 30 days.
English version:

Chinese version:
https://www.jetbrains.com/zh-cn/idea/download/?section=windows
Installation tutorial:


| username: come_true | Original post link

Sure, using the community edition is fine.

| username: come_true | Original post link

Thank you for sharing.

| username: come_true | Original post link

Could you please share the link?