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
– Akina, Commented Jun 10, 2021 at 4:36

Rick James · Accepted Answer · 2021-06-10 06:34:08Z

3

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?

answered Jun 10, 2021 at 6:34

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jaried Over a year ago

I made a comparison, inserting a few thousand rows does not need to delete the index.

Albert Alberto · Accepted Answer · 2021-06-10 04:51:10Z

3

Drop the index
Insert your data
Create your index

answered Jun 10, 2021 at 4:51

Albert Alberto

97810 silver badges15 bronze badges

2 Comments

Akina Over a year ago

Not applicable to primary key or the index which is treated as primary.

Rick James Over a year ago

I predict this to be slower than simply inserting the rows -- since it is only 0.1% of the total.

Collectives™ on Stack Overflow

How to disable index in innodb in mysql

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related