What happens to indexes during bulk inserts, and how can you optimize performance for such operations?

Question

Accepted Answer

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** ```sql 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 = 2` during imports.   • `innodb_buffer_pool_size` should be large to fit indexes.  **h. Use LOAD DATA INFILE Instead of INSERT**   • Fastest option for bulk loading.   • Optimized internal engine operations. ALTER TABLE sales DROP INDEX idx_customer;
ALTER TABLE sales DROP INDEX idx_date;

LOAD DATA INFILE 'sales.csv' INTO TABLE sales;

ALTER TABLE sales ADD INDEX idx_customer(customer_id);
ALTER TABLE sales ADD INDEX idx_date(order_date); 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.

What happens to indexes during bulk inserts, and how can you optimize performance for such operations?

What Happens to Indexes During Bulk Inserts and How to Optimize Performance

What happens to indexes during bulk inserts, and how can you optimize performance for such operations?

What Happens to Indexes During Bulk Inserts and How to Optimize Performance

Scenario Questions

Follow-up Questions