Bulk Loading

Loading millions of rows: batch the statements, wrap them in a transaction, and defer index and constraint work until the end.

SyntaxSET autocommit = 0, unique_checks = 0, foreign_key_checks = 0; /* load */ COMMIT;

The cost of inserting one row is dominated by fixed overheads — parsing the statement, flushing the log, maintaining every index. Bulk loading is the practice of amortising those overheads over many rows.

Batch the statements

A single INSERT with many VALUES lists is, per the manual, "many times faster in some cases" than the same rows as separate statements, because the parse and round trip happen once. When the data is already in a file, LOAD DATA is faster still — the manual puts it at roughly 20 times the speed of equivalent INSERT statements. LOAD DATA LOCAL reads the file from the client and requires the local_infile system variable to be enabled on the server.

Wrap it in a transaction

With autocommit on, every statement forces a log flush to disk. Set autocommit = 0, load, then COMMIT once.

Defer the index and constraint work

For InnoDB, SET unique_checks = 0 lets the change buffer write secondary index records in batches, and SET foreign_key_checks = 0 skips the parent lookups. Insert in PRIMARY KEY order, since InnoDB stores rows in that order in a clustered index. Build secondary and FULLTEXT indexes after the load, not before. ALTER TABLE ... DISABLE KEYS is a MyISAM feature and does nothing useful for InnoDB.

Example

-- All three are session-scoped: they affect this connection only.
SET autocommit = 0;
SET unique_checks = 0;      -- only if you have verified the file has no duplicate keys
SET foreign_key_checks = 0; -- parent rows must still genuinely exist; nothing rechecks later

-- LOCAL reads the file on the client side and needs local_infile enabled on the server.
LOAD DATA LOCAL INFILE '/data/events_2026_09.csv'
  INTO TABLE events
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES  TERMINATED BY '\n'
  IGNORE 1 LINES                                  -- skip the CSV header row
  (event_uuid, user_id, @raw_ts, payload)         -- @raw_ts is a user variable, not a column
  SET occurred_at = STR_TO_DATE(@raw_ts, '%Y-%m-%d %H:%i:%s');

COMMIT;

-- Restore the session before doing anything else on this connection.
SET foreign_key_checks = 1;
SET unique_checks = 1;
SET autocommit = 1;

-- LOAD DATA reports: Records: N  Deleted: 0  Skipped: 0  Warnings: N
SHOW WARNINGS;   -- read these; skipped rows are warnings, not errors

When to use it

Import a multi-gigabyte CSV export into a new table overnight.
Backfill a denormalised reporting table from live tables in bounded chunks.
Seed a staging or test database from a dump without waiting hours.
Reload a table after a schema change, building its secondary indexes last.

More examples

Load first, index afterwards

This is the shape of a large migration or a full reload of a derived table. Sorting the input by primary key before loading matters more than it sounds: InnoDB keeps rows in clustered-index order, so out-of-order inserts cause page splits, and the penalty grows once the table no longer fits in the buffer pool. The RENAME at the end keeps the cutover instantaneous.

-- Create the table with only the clustered PRIMARY KEY. Every secondary index
-- present during the load is another B-tree to maintain per row.
CREATE TABLE events_new (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  occurred_at DATETIME        NOT NULL,
  payload     JSON            NOT NULL
) ENGINE=InnoDB;

-- ... bulk load here, in PRIMARY KEY order ...

-- Build the secondary indexes once, over the finished data.
CREATE INDEX idx_events_user_time ON events_new (user_id, occurred_at);

-- Swap the new table in. RENAME TABLE is atomic: no window with no table.
RENAME TABLE events TO events_old, events_new TO events;

Batching INSERTs from application code

Use this when the data arrives over the network rather than as a file, which rules out LOAD DATA. The two knobs are batch size per statement and statements per transaction: too small and you are back to per-row overhead, too large and a single failure rolls back a lot of work while the undo log grows. Committing every few tens of thousands of rows is a reasonable starting point.

-- One statement, 1,000 rows: one parse, one round trip, one log flush.
-- Build this string in your application; keep the batch size in the low thousands
-- so the statement stays under max_allowed_packet.
INSERT INTO events (event_uuid, user_id, occurred_at, payload)
VALUES
  ('0f1c...', 1042, '2026-09-17 09:14:02', '{"k":1}'),
  ('2b7e...', 1042, '2026-09-17 09:14:07', '{"k":2}'),
  -- ... 998 more ...
  ('91aa...', 1188, '2026-09-17 09:15:51', '{"k":3}');

-- Batch the batches: commit every N statements rather than every row,
-- and rather than once for the whole ten-million-row job.
START TRANSACTION;
  /* 50 x the statement above */
COMMIT;

Server-side reload with no client round trip

The pattern for backfilling a reporting or cache table. Chunking on the primary key rather than on OFFSET matters: OFFSET makes the server scan and discard everything before the window, so the job gets slower with every batch, while a key range stays constant-cost. Record the last completed range somewhere so an interrupted job can resume.

-- Rebuilding a denormalised table from tables already in the database.
-- Nothing crosses the network, so this is far faster than SELECT-then-INSERT.
SET autocommit = 0;

INSERT INTO order_summary (order_id, customer_id, item_count, total_cents)
SELECT o.id,
       o.customer_id,
       COUNT(oi.id),
       SUM(oi.qty * oi.unit_cents)
FROM   orders AS o
JOIN   order_items AS oi ON oi.order_id = o.id
WHERE  o.id BETWEEN 1 AND 500000     -- chunk by primary key range
GROUP  BY o.id, o.customer_id;

COMMIT;
SET autocommit = 1;

-- Repeat with the next range. Chunking keeps each transaction's undo log
-- and lock footprint bounded, and makes a failure cheap to retry.

Discussion

Be the first to comment on this lesson.