Boost MySQL Bulk Inserts: Proven Techniques & Optimized Syntax

Factors Influencing INSERT Speed

Inserting a single row consists of several time components (approximate weight in parentheses): connection (3), sending query to server (2), query parsing (2), writing the row (1 × row size), updating indexes (1 × index count), and commit/close (1). The cost of index insertion grows with table size at a logN rate due to B‑tree structures.

Methods to Accelerate Inserts

When many rows are inserted from the same client, use a single INSERT statement with multiple VALUE tuples; this can be several times faster than issuing individual statements. Adjust bulk_insert_buffer_size for non‑empty tables to improve speed.

From different clients, INSERT DELAYED can increase throughput. With MyISAM tables that have no deleted rows, inserts can occur concurrently with SELECT queries.

For bulk loading from a text file, LOAD DATA INFILE is typically 20× faster than many INSERT statements. When many indexes exist, further speed gains are possible by:

Running FLUSH TABLES or mysqladmin flush‑tables;

Disabling index usage with myisamchk –keys-used=0 -rq /path/to/db/tbl_name before loading;

Using LOAD DATA INFILE to load data without updating indexes, then optionally compressing the table with myisampack;

Rebuilding indexes in memory via myisamchk -r -q /path/to/db/tbl_name before the data is flushed to disk.

Alternatively, replace the myisamchk steps with ALTER TABLE tbl_name DISABLE KEYS before loading and ALTER TABLE tbl_name ENABLE KEYS after loading, which also skips the FLUSH TABLES step.

Locking Tables for Bulk Inserts

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 prevents index cache flushes after each INSERT, improving performance. For transactional tables, use BEGIN and COMMIT instead of explicit table locks.

Example of concurrency impact:

Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert each
Connection 5 does 1000 inserts

Without locking, connections 2‑4 finish before 1 and 5; with locking, overall time can improve by roughly 40% despite some connections waiting.

Using DELAYED

The DELAYED modifier applies to INSERT and REPLACE statements. Rows are queued and the client receives an immediate response while the server inserts them later. The queue is processed only when no readers are accessing the table. Limitations include:

Only works with value‑list INSERT, not with INSERT … SELECT or INSERT … ON DUPLICATE KEY UPDATE; LAST_INSERT_ID() cannot be used because the statement returns before the row is written;

Rows are invisible to SELECT until they are actually inserted;

Queued rows exist only in memory and are lost if the server crashes before flushing to disk.

Using IGNORE

IGNORE

is a MySQL extension that skips rows causing duplicate‑key errors or warnings (in STRICT mode). When a duplicate occurs, the first row is kept and the conflicting rows are discarded, allowing the statement to continue without aborting.

ON DUPLICATE KEY UPDATE

If a unique or primary key conflict occurs, the existing row is updated instead of causing an error. Example:

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

When the row is newly inserted, the affected‑row count is 1; when an existing row is updated, the count is 2.

For tables with multiple unique keys, avoid using this clause because only one matching row will be updated. The VALUES(col_name) function can reference the inserted values within the UPDATE part, which is useful for multi‑row inserts:

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

The above statement is equivalent to executing two separate INSERT … ON DUPLICATE KEY UPDATE statements with the respective values.

Note: When ON DUPLICATE KEY UPDATE is used, the DELAYED option is ignored.