How to disable index in innodb in mysql

Question

My mysql version is 8.0. I have an InnoDB table with more than 10 million rows of data, and the data file size is 2G.

I am using pd.to_sql to insert several thousand rows of data each time. When the index is enabled, the insertion speed is very slow.

How to temporarily disable the index? And after the insertion is complete, enable the index?

I tried the operation in How to disable index in innodb:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

But the index is still valid.

*How to temporarily disable the index?* Noway. You may drop the index and recreate it after insertion completed. PS. Primary key (which is clustered) must be stored anycase - if you drop it with another indices then complete table rewriting will occur during its re-creation. — Akina, Jun 10 '21 at 04:36

score 3 · Answer 1 · answered Jun 10 '21 at 04:51

3

Drop the index
Insert your data
Create your index

answered Jun 10 '21 at 04:51

Albert Alberto

801
8
15

1

Not applicable to primary key or the index which is treated as primary. – Akina Jun 10 '21 at 05:12
1

I predict this to be slower than simply inserting the rows -- since it is only 0.1% of the total. – Rick James Jun 10 '21 at 06:34

score 3 · Accepted Answer · answered Jun 10 '21 at 06:34

No, something else is going wrong.

Please provide more details of the code involved. Inserting a few thousand rows in a 10M-row table will not benefit from the high cost of rebuilding the index.

Please provide SHOW CREATE TABLE so we can see the indexes. UNIQUE indexes are handled differently. FOREIGN KEYs add overhead. Batching the rows helps a lot, but 10K rows is on the high-end of what is efficient. What is the PRIMARY KEY? Are the rows being inserted in any predictable order?

I made a comparison, inserting a few thousand rows does not need to delete the index. — jaried, Jun 10 '21 at 06:50

How to disable index in innodb in mysql

2 Answers2