In this lesson, you will learn how to drastically reduce the time required to ingest millions of rows by using batch processing and strategic index management.
When you perform a massive INSERT operation, the database engine has to do more than just write data to disk. For every single row inserted, the storage engine must also update the B-Tree structures for every index associated with that table. If you have five indexes, a single row insertion requires six physical write operations (one for the base data, five for the index pages), leading to heavy I/O overhead and constant page splitting.
Strategy 1: Batch Processing
Instead of firing thousands of individual INSERT statements, group your data into chunks (typically 1,000 to 5,000 rows per statement). This reduces the overhead of transaction logging and network round-trips.
-- Instead of 5000 separate INSERTs:
INSERT INTO sensor_data (id, value, ts) VALUES (1, 22.5, '2023-01-01'), (2, 23.1, '2023-01-01'), ...;
Strategy 2: Index Disabling
For truly massive imports, the most effective optimization is to minimize index maintenance. While MySQL/MariaDB doesn’t have a simple "disable index" button for InnoDB tables like some other engines, the industry-standard workflow is:
- Drop non-unique indexes before starting the import.
- Perform the bulk load in batches.
- Re-create the indexes after the data is loaded.
When you create an index on a table that already contains data, the database performs a "bulk load" or "sort-and-build" operation. This is significantly more efficient than updating the B-Tree iteratively because the engine can sort the data in memory and build the tree structure from the bottom up, resulting in a perfectly balanced index without the performance penalty of frequent page splits.
Key Takeaway: To optimize large imports, batch your data to reduce transaction overhead and drop secondary indexes before loading, re-creating them only after the data is fully populated to leverage efficient bulk-indexing algorithms.