MySQL Insert Speeds Vary by 103x: The Same 100k Rows, Different Methods

Same 100k Rows Inserted: Fastest Method Is 103x Faster Than Slowest

Method                               Time        Throughput (rows/s)    Relative to slowest
-----------------------------------------------------------------
Single-row INSERT, one txn each      52.54 s        1,903        1.0x
Single-row INSERT, one big txn       16.06 s        6,228        3.3x
Batch INSERT, 100 rows/batch          1.49 s       66,951       35.2x
Batch INSERT, 1000 rows/batch         1.42 s       70,179       36.9x
LOAD DATA LOCAL INFILE                0.51 s      197,028      103.5x

52 seconds vs 0.51 seconds. Same data, same table structure, only the writing method differs.

(This is with a local Unix socket connection. Add production network round trips and the first row gets much worse — recall the earlier N+1 benchmark: a 17x local gap became 170x at 1ms RTT.)

Layer 1: Transactions

One txn per row     1,903 rows/s
One big txn         6,228 rows/s      3.3x

With default autocommit=1, each INSERT is a full transaction: begin, write undo log, write redo log, commit, fsync.

That fsync is the big cost. Loosening innodb_flush_log_at_trx_commit in a real test:

flush=1   2,380 rows/s    fsync on every commit (safest, default)
flush=2   4,124 rows/s    write to OS cache, fsync once per second       1.7x
flush=0   6,005 rows/s    write once per second + fsync per second       2.5x

fsync accounts for 2.5x of per-row commit overhead.

But note: putting 100k rows into a single transaction only gets 6,228 rows/s, while batch INSERT gets 70,000 rows/s. That means after eliminating fsync, another 11x gap remains unexplained.

Layer 2: Per-SQL Fixed Overhead

That remaining 11x comes from the cost of each individual SQL statement:

• One client → server round trip (even over a Unix socket, it's a syscall)
• Lexical and syntax analysis of the SQL text
• Query optimizer pass (even for the simplest INSERT)
• Permission checks, table metadata lookups
• One InnoDB row insert + index maintenance

That long list has nothing to do with "inserting a row" itself, but you pay it every time.

Batch INSERT merges 1000 rows into one SQL:

sql

INSERT INTO t(uid, city, amount) VALUES
  (1,'北京',100),
  (2,'上海',200),
  ... 998 more rows

Parse once, optimize once, round-trip once, then insert 1000 rows consecutively. The fixed overhead is amortized 1000x.

Batch Size: Gains Saturate at 100

Rows per batch   Time      Throughput (rows/s)   Relative to single   SQL length
-----------------------------------------------------------------------------
    1         15.65 s        6,389        1.0x        —
   10          2.78 s       35,970        5.6x       1 KB
   50          1.76 s       56,755        8.9x       5 KB
  100          1.53 s       65,270       10.2x       9 KB
  500          1.40 s       71,591       11.2x      46 KB
 1000          1.43 s       69,952       10.9x      93 KB
 5000          1.52 s       65,971       10.3x     464 KB
20000          1.41 s       70,692       11.1x    1855 KB

The biggest jump is from 1 to 10 — 5.6x. Then it quickly converges; at 100 you get 10.2x, and after 500 it's a plateau.

Practical advice is simple: pick a batch size between 100 and 1000, no need to agonize.

Chasing larger batches has risks:

• Hitting max_allowed_packet (MariaDB default 16 MB, MySQL 8 default 64 MB), erroring with Packet too large
• Oversized transactions — undo log bloat, longer lock holds, replication lag
• Whole batch rollback on failure, making retries expensive

Layer 3: LOAD DATA's 197,028 rows/s

LOAD DATA LOCAL INFILE    0.51 s    197,028 rows/s    103.5x

That's 2.8x faster than batch INSERT.

Because it doesn't go down the SQL path at all:

• No SQL parsing — data is split by delimiters, not by syntax tree
• Streaming read — no need to build a giant string of the whole batch in memory
• Bulk index building — can maintain the B+ tree more efficiently
• Fewer internal locks acquired

The cost is that it's rough:

• File format must strictly align with the table structure
• Weak error handling (one malformed line either fails the whole batch or silently becomes a default value)
• The LOCAL keyword involves the client reading a file, which has security implications (MySQL 8 disables local_infile by default; you must explicitly enable it)

So its place is data import, offline migration, ETL loading — not online writes.

A Selection Table

Scenario	What to use	Expected throughput
Online single writes (orders, signups)	Single INSERT	No optimization needed; it's already single-row
Batch jobs, message consumption	Batch INSERT, 100~1000 per batch	Tens of thousands of rows/sec
Data import, migration, backfill	LOAD DATA	Hundreds of thousands of rows/sec
Upsert with known primary keys	Batch INSERT ... ON DUPLICATE KEY UPDATE	Close to batch INSERT

A few practical points:

1. Drop indexes first, load, then rebuild. For large imports, maintaining 5 secondary indexes row-by-row is expensive (from the earlier index benchmark, 4 secondary indexes cut write throughput by 33%). Drop indexes, load, then ADD INDEX to build once — usually faster.

2. Insert in primary key order. From the earlier UUID primary key benchmark, random keys make inserts 11.8x slower. Sort by primary key before the bulk import, and the payoff is big.

3. An ORM's "bulk" isn't necessarily batch. Many ORMs' bulk_create / saveAll just loop single INSERTs inside one transaction — that only gets you 3.3x, not 36x. Check the logs to confirm it's emitting multi-value INSERTs.

4. rewriteBatchedStatements=true. The JDBC MySQL driver by default does not merge addBatch calls into multi-value INSERTs; you must explicitly set this connection parameter. This is the most common "I thought I was batching" trap in the Java ecosystem.

Finally

The 52-second vs 0.51-second gap isn't in the database, and isn't in the disk.

It's whether you talked to the database 100,000 times, or once.