Boost MySQL Bulk Inserts: Proven Techniques & Optimized Statements

Speed of INSERT Statements

Inserting a row in MySQL involves several steps, each contributing to overall latency. Approximate weightings are: connection (3), sending query (2), parsing query (2), inserting the row (1 × row size), inserting index entries (1 × index), and closing (1). Table size also affects index insertion speed, growing roughly with log N due to B‑tree structures.

Methods to Accelerate Inserts

• Use multi‑row INSERT with many VALUES in a single statement; this can be several times faster than single‑row inserts. Adjust bulk_insert_buffer_size for non‑empty tables.

• From multiple clients, employ INSERT DELAYED to queue rows.

• With MyISAM tables that have no deleted rows, inserts can run concurrently with SELECTs.

• For bulk loading from a text file, use LOAD DATA INFILE, typically 20× faster than many INSERTs. When many indexes exist, consider the following process to speed it up.

Selective Table Creation with CREATE TABLE

Run FLUSH TABLES or mysqladmin flush‑tables, then disable index usage with myisamchk –keys-used=0 -rq /path/to/db/tbl_name. Load data with LOAD DATA INFILE (no index updates). After loading, optionally compress the table with myisampack for read‑only use.

Rebuild indexes efficiently using myisamchk -r -q /path/to/db/tbl_name, which builds the index tree in memory before writing to disk, avoiding extensive disk seeks.

Alternatively, replace the above with ALTER TABLE tbl_name DISABLE KEYS before loading and ALTER TABLE tbl_name ENABLE KEYS after loading, skipping the explicit FLUSH TABLES step.

Table Locking to Speed Multiple INSERT Statements

Example:

LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;

Locking reduces the number of index‑cache flushes because the cache is written once after all INSERTs complete. If all rows can be inserted in a single statement, locking is unnecessary.

For transactional tables, use BEGIN and COMMIT instead of explicit table locks.

Locking can also lower overall time in multi‑connection scenarios; a simple test shows about a 40% speed gain when appropriate locks are used.

Using INSERT, UPDATE, and DELETE Efficiently

When many rows are inserted or updated in a single transaction, locking the table yields better overall performance. However, INSERT statements are generally faster than LOAD DATA INFILE only when the latter’s optimizations are not applied.

Increasing key_buffer_size improves both LOAD DATA INFILE and INSERT performance on MyISAM tables.

INSERT Syntax Variants

Standard form:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ON DUPLICATE KEY UPDATE col_name=expr, ...]

Alternative forms include using SET col_name={expr|DEFAULT}, ... or inserting from a SELECT.

DELAYED Modifier

INSERT DELAYED

queues rows on the server, returning immediately to the client while the server inserts rows in the background. It works only with simple value‑list INSERTs; it is ignored for INSERT … SELECT, INSERT … ON DUPLICATE KEY, and on replica servers. Because rows are not written to disk immediately, they can be lost if the server crashes.

IGNORE Modifier

Adding IGNORE causes MySQL to skip rows that would cause duplicate‑key errors, inserting only the first occurrence and adjusting erroneous values where possible. This avoids aborting the whole statement on duplicate keys.

ON DUPLICATE KEY UPDATE

If a UNIQUE or PRIMARY KEY conflict occurs, MySQL updates the existing row instead of inserting a new one. Example:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c = c + 1;

The VALUES(col_name) function can reference the inserted values within the UPDATE clause, which is useful for multi‑row inserts.

When this clause is used, the DELAYED option is ignored.

These techniques together provide a comprehensive toolbox for speeding up large‑scale data insertion in MySQL.