Is it possible to temporarily disable an index?

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

Original topic: 是否支持暂时让索引失效

| username: vincentLi

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.3
Migrating a table with 500 million rows is too inefficient, only 20,000 rows per minute. Is it possible to temporarily disable the index and re-enable it after the data is migrated to the new table?

| username: TIDB-Learner | Original post link

  1. Creating an index first and generating the index when inserting data is time-consuming. 2. Inserting data first and then manually creating the index is also time-consuming. Which of the two is better? Creating the index after inserting the data is better. Both are quite time-consuming.
| username: tidb菜鸟一只 | Original post link

What’s the difference between this and deleting the index first and then creating it later… Anyway, the index data needs to be regenerated…

| username: zhaokede | Original post link

Creating an index is quite fast.
Index invalidation is generally used in business scenarios where it is difficult to determine whether the index is still being used.

| username: vincentLi | Original post link

In large-scale operations, the difference is significant. To invalidate an index, I only need to remember the index name and change it. For re-keying, I need to unload the re-keying statement as it is and execute it later.

| username: vincentLi | Original post link

The experience with ORACLE shows that there are still significant differences in scenarios involving large-scale data insertion.

| username: zhanggame1 | Original post link

Delete and recreate the index. Increasing the concurrency parameters when creating the index will make it much faster.

| username: zhaokede | Original post link

“Invisibility” is only for the optimizer; invisible indexes can still be modified or deleted. The manual also doesn’t mention whether invisible indexes will be updated when a large amount of data is written. If they are updated, then the operation method mentioned by the original poster does not achieve the intended purpose. Has anyone tested this operation method?

| username: forever | Original post link

Index failure, inserting data does not maintain the index, and finally rebuilding the index is the same as deleting the index.
Index invisibility, as the previous poster mentioned, it is just invisible, but inserting data still maintains the index simultaneously.

| username: yulei7633 | Original post link

For the business, it can be temporarily disabled, but in your case, it is best to delete it first, then create a new index after the migration is complete.

| username: 霸王龙的日常 | Original post link

Disabling the index temporarily only makes the optimizer not use the index. The index is still maintained during actual data insertion, so the insertion efficiency does not change. It is recommended to delete the index first, insert the data, and then rebuild the index, which is quite fast.

| username: 我是人间不清醒 | Original post link

Delete and rebuild it. Since you are using version 6.5, the rebuilding process will be slower.

| username: tony5413 | Original post link


| username: lemonade010 | Original post link

Invisible indexes are only unavailable for execution plans, the indexes are still there. It’s more appropriate to delete and rebuild them.

| username: TiDBer_vJGTQABF | Original post link

First delete the new table index, then create it after data migration.

| username: vincentLi | Original post link

Thank you, everyone. I found that the low efficiency was not actually caused by the index, but probably because batch insert was not used. After optimization, achieving 20,000 to 50,000 in under 10 seconds is still acceptable. Thanks, everyone. I also learned about invisible indexes, which should have significant differences from Oracle invalid indexes. Oracle invalid indexes can be rebuilt online, which is quite convenient.

| username: 小于同学 | Original post link

Creating an index for 500 million records probably won’t be very fast either.

| username: 哈喽沃德 | Original post link

Why not create the index on the target end after the data migration is complete?

| username: TiDBer_QKDdYGfz | Original post link

I learned something new, there’s also the usage of “invisible”.

| username: 我是吉米哥 | Original post link

Does TIDB have an index monitoring feature that periodically identifies unused indexes and then deletes them?