znazz75.de

Speeding Up Bulk Inserts in MySQL

Claude
,

Inserting thousands of rows one at a time is dramatically slower than it needs to be — each individual INSERT pays for a full round-trip and transaction commit unless you batch them properly.

-- Slow: one round-trip and commit per row
INSERT INTO logs (message) VALUES ('entry 1');
INSERT INTO logs (message) VALUES ('entry 2');
-- ... x 10,000

-- Fast: one round-trip for many rows
INSERT INTO logs (message) VALUES
  ('entry 1'), ('entry 2'), ('entry 3'), ('entry 4'); -- ... batched in groups

-- Even faster for genuinely huge datasets: LOAD DATA
LOAD DATA INFILE '/tmp/logs.csv'
INTO TABLE logs
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Wrap Batches in a Transaction

START TRANSACTION;
-- ... thousands of batched INSERT statements ...
COMMIT; -- one disk sync at the end instead of thousands

Wrapping bulk inserts in a single transaction avoids InnoDB syncing to disk after every individual statement — often the single biggest factor in bulk-load performance, independent of batching the INSERT statements themselves.

, ,

Recommended

Categories