The Impact of TiDB Index Merge Feature on Performance

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

Original topic: TIDB 索引合并特性对性能的影响

| username: residentevil

[TiDB Usage Environment] Production Environment
[TiDB Version] V6.5.8
[Encountered Problem: Problem Phenomenon and Impact] According to the official documentation, index merge supports two types: intersection and union. Has anyone’s business line SQL used this feature? How effective is it?

| username: TiDBer_RjzUpGDL | Original post link

When there are too many indexes involved or the data distribution is uneven, the performance is poor.

| username: zhaokede | Original post link

I haven’t used it before; usually, I optimize tables or rebuild indexes.

| username: residentevil | Original post link

In the case of “or” scenarios, if it is an intersection, the performance might be better. It’s hard to say for a union.

| username: zhang_2023 | Original post link

Generally, it involves rebuilding the index.

| username: residentevil | Original post link

What does rebuilding an index mean?

| username: zhanggame1 | Original post link

I don’t quite understand.

| username: redgame | Original post link

Haven’t used it yet…

| username: zhaokede | Original post link

It mainly depends on the business needs; sometimes a composite index is created directly.

| username: residentevil | Original post link

For cases where the filter condition includes “or,” this feature is still needed.

| username: zhaokede | Original post link

This requires the use of index merge union.

| username: 小于同学 | Original post link

I haven’t dealt with it. How do you merge indexes?

| username: residentevil | Original post link

From the official documentation, it seems that the intersection and union of AND and OR filter conditions can be merged. The general idea is that a query SQL can use both types of INDEX simultaneously, and the results returned by the indexes are then merged. I’m not sure if this understanding is correct.

| username: FutureDB | Original post link

In general, OR scenarios are still somewhat useful. For AND scenarios, it’s better to create a composite index because specifying the execution plan is relatively difficult to maintain.

| username: TiDBer_aaO4sU46 | Original post link

Haven’t used it…

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

If there are many AND conditions, it’s better to use a composite index directly. If there are many OR conditions, index merging is more useful.

| username: TiDBer_rvITcue9 | Original post link

Don’t know

| username: system | Original post link

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