What Happens to Indexes During Bulk Inserts and How to Optimize Performance
Bulk inserts can put heavy load on MySQL because every inserted row must also update all related indexes. The more indexes a table has, the slower bulk inserts become. MySQL provides several techniques to optimize bulk insert performance, especially for large datasets.
Each row insertion requires updating every B-tree index.
Indexes may become fragmented due to many random insert positions.
Large secondary indexes slow down bulk inserts significantly.
InnoDB logs index changes in the redo log, increasing I/O load.
Unique indexes require additional checks, making inserts slower.
Each index requires a B-tree search + write.
More indexes = more writes and more page splits.
Unique indexes add extra validation overhead.
Large indexes may not fit in memory, causing extra disk access.
a. Disable or Drop Indexes Before Bulk Inserts (Best for MyISAM)
• MyISAM supports fast index rebuilding after bulk insert.
• Much faster than updating indexes row-by-row.
b. For InnoDB: Use ALTER TABLE … DISABLE KEYS (No Effect for InnoDB)
• DISABLE KEYS works only for MyISAM, not InnoDB.
c. Temporarily Drop Secondary Indexes and Recreate Them After Insert
• Very effective for massive inserts.
• Rebuilding one large index is faster than updating millions of rows.
d. Insert Rows in Primary Key Order
• Reduces page splits in the clustered index.
• Improves buffer pool efficiency.
e. Use Bulk Insert Statements
• Use multi-row insert batches (e.g., 5k–20k rows per batch).
f. Disable Foreign Key Checks Temporarily
SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;
• Reduces integrity-check overhead.
g. Set InnoDB to Use Minimal Logging
•
innodb_flush_log_at_trx_commit = 2during imports.•
innodb_buffer_pool_sizeshould be large to fit indexes.h. Use LOAD DATA INFILE Instead of INSERT
• Fastest option for bulk loading.
• Optimized internal engine operations.
Rebuilding an index is a single large operation with sequential writes.
Bulk rebuilding is faster than incrementally updating per-row.
Rebuilds reduce fragmentation and produce more compact indexes.
In summary, bulk inserts can become slow due to index maintenance overhead. You can significantly improve performance by minimizing indexes during import, inserting in primary key order, using multi-row inserts, and leveraging optimized loading mechanisms like LOAD DATA INFILE.