Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: OR条件为何不走索引合并？？
[TiDB Usage Environment] Production Environment
[TiDB Version] v6.1
[Encountered Issue: Problem Phenomenon and Impact]
When using select .. from a join b on a.primary_key = b.unique_key where a.time >= xxx OR b.time >= xxx (both time fields are indexed), the query is very slow. After checking the explain plan, it seems that index merge is not being utilized.
The specific SQL is as follows:
SELECT
a.uid,
CASE
WHEN a.reg_area IN (111, 0) THEN
b.quyubh
ELSE
a.reg_area
END reg_area,
CASE
WHEN location_area = 0
AND REGIONCODE IS NOT NULL THEN
REGIONCODE
ELSE
a.location_area
END location_area
FROM
`dwd`.`user_extend` a
JOIN `dwd`.`user_first_location` b ON b.zhanghao = a.uid
WHERE
a.update_time >= '2023-03-06 00:00:00' OR b.update_time >= '2023-03-06 00:00:00';
[Attachment: Screenshot/Logs/Monitoring]
In the case of OR, the execution plan is as follows:
Projection_9 19376062.00 root dwd.user_extend.uid, case(in(dwd.user_extend.reg_area, 912000000041844186, 0), dwd.user_first_location.quyubh, dwd.user_extend.reg_area)->Column#84, case(and(eq(dwd.user_extend.location_area, 0), not(isnull(dwd.user_first_location.regioncode))), dwd.user_first_location.regioncode, dwd.user_extend.location_area)->Column#85
└─Projection_10 19376062.00 root dwd.user_extend.uid, dwd.user_extend.reg_area, dwd.user_extend.location_area, dwd.user_first_location.quyubh, dwd.user_first_location.regioncode
└─IndexJoin_16 19376062.00 root inner join, inner:TableReader_13, outer key:dwd.user_first_location.weimaihao, inner key:dwd.user_extend.uid, equal cond:eq(dwd.user_first_location.weimaihao, dwd.user_extend.uid), other cond:or(ge(dwd.user_extend.update_time, 2023-03-06 00:00:00.000000), ge(dwd.user_first_location.update_time, 2023-03-06 00:00:00.000000))
├─TableReader_45(Build) 19376062.00 root data:Selection_44
│ └─Selection_44 19376062.00 cop[tikv] not(isnull(dwd.user_first_location.weimaihao))
│ └─TableFullScan_43 19398524.00 cop[tikv] table:b keep order:false
└─TableReader_13(Probe) 1.00 root data:TableRangeScan_12
└─TableRangeScan_12 1.00 cop[tikv] table:a range: decided by [dwd.user_first_location.weimaihao], keep order:false
If you use the OR condition, it’s definitely impossible to use the index first. If the index is used first, what happens to the values that are filtered out and can be associated? So, it definitely scans the entire tables of both tables first, and then filters. The subsequent filtering definitely won’t use the index.
So, is the prerequisite for index merge an OR query condition on a single table? It won’t perform index merge for two tables in a multi-table join, right?
Change it to filter first, so the index will take effect. Then aggregate, it will be faster with a smaller range.
If you change it to “and,” you can filter it. After joining the two tables, the “or” condition filtering definitely won’t use index merging.
Switch to using IN: WHERE a.update_time IN ('2023-03-06 00:00:00', '2023-03-06 00:00:00')
That definitely can use indexes. It seems that index merge cannot be used for join queries, right?
I changed it a few times, but the results still seem incorrect… Can you please tell me how to fix it? I’m not very good at SQL 
Is it to directly split the OR and then write it as two SQL statements with a union added? It seems the result is correct, and each uses an index. Is this the correct way to modify it?
If both sides of the OR condition on the same table have indexes, it should be able to use index_merge. For multi-table joins, it probably won’t be applicable.
You can try using index merge
Tried it, didn’t work. Index merge should only be applicable to a single table.
Yes, I tried it and it does seem that index merge is available for single tables.
This works, tested and confirmed.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: 海石花47
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: