Frontend

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

By 小卿噢 · Oct 29, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Developers routinely leave 10x–100x on the table in MySQL ingest because they assume the database is the bottleneck. The measured truth is that per-SQL round trips, fsyncs, and naive ORM wrappers cost more than the row insertion itself.

Summary

A direct benchmark of 100k-row inserts into MySQL puts the slowest method (individual INSERT with a transaction per row) at 1,903 rows/s, and the fastest (LOAD DATA LOCAL INFILE) at 197,028 rows/s — a 103.5x spread. The bottleneck isn't the disk or database engine; it's how many times you talk to the server and how often you force an fsync.

Batch INSERT with 100–1000 rows per statement captures most of the gain, hitting about 70k rows/s. The jump from one row to ten yields 5.6x; beyond 100 rows, throughput plateaus. LOAD DATA skips SQL parsing and streaming builds indexes, which is why it reaches 2.8x even beyond well-tuned batch INSERT, but it trades strict file format and weaker error handling. Practical tips from the tests: drop secondary indexes before bulk loads, sort by primary key, verify that ORM "bulk" APIs actually emit multi-value INSERTs, and set rewriteBatchedStatements=true in JDBC to avoid silently falling back to single-row transactions.

Takeaways

— Per-row INSERT with one transaction per row: 52.54s, 1,903 rows/s.

— Wrapping all 100k rows in a single transaction only lifts throughput to 6,228 rows/s (3.3x).

— fsync per commit accounts for a 2.5x slowdown; the remaining 11x comes from per-SQL parsing, optimization, round trips, and index maintenance.

— Batch INSERT with 100–1000 rows per statement reaches about 70k rows/s, a 36x gain over per-row.

— Batch size returns mostly at 10 rows (5.6x) and saturates around 100–500; larger batches risk max_allowed_packet, transaction bloat, and full-batch rollback.

— LOAD DATA LOCAL INFILE hits 197k rows/s (103.5x) by skipping SQL parsing and streaming index builds, but requires strict file alignment and weaker error handling.

— Dropping secondary indexes before a bulk load and rebuilding after avoids up to 33% write throughput loss.

— Random primary keys slow inserts by 11.8x; sorting by primary key before bulk load prevents that.

— Many ORM bulk_create/saveAll calls only do single-row INSERTs inside one transaction, yielding 3.3x, not 36x; confirm multi-value INSERTs in logs.

— JDBC's MySQL driver does not merge addBatch into multi-value INSERT unless rewriteBatchedStatements=true is set.

— Online single-row writes don't need optimization; batch INSERT fits batch jobs; LOAD DATA is for imports and ETL.

Conclusions

The 2.5x fsync penalty is real, but it's a side show — the dominant cost is the fixed per-SQL overhead, which is why batching beats merely widening the transaction.

Because batch returns flatten at 100 rows, developers gain nothing from risking huge multi-megabyte statements; 100–1000 is the sweet spot for both performance and operational safety.

The ORM trap is the most expensive mistake in practice: code that looks like batching but still emits single-row INSERTs quietly leaves 10x on the table, and only query logging exposes it.

LOAD DATA's 2.8x advantage over even optimal batch INSERT shows that the SQL layer itself carries a heavy tax; formats that skip the parser are worth considering for any high-volume ingest path.

The main caveat — missing indexes and random primary keys can erase the entire gain from LOAD DATA — implies that physical layout and index strategy remain the real ceiling for write throughput, not the chosen insert syntax.

Concepts & terms

fsync

A system call that forces dirty data in OS cache to be written to durable storage. In MySQL, per-transaction fsync makes commits wait on physical disk writes, which is the dominant cost in single-row autocommit inserts.

undo log / redo log

InnoDB's write-ahead logs. The undo log tracks how to reverse changes for rollback; the redo log records changes for crash recovery. Every transaction writes both, adding fixed per-transaction overhead.

autocommit

MySQL's default mode where each statement is wrapped in its own transaction and committed immediately, forcing an fsync per INSERT and multiplying the overhead when inserting row by row.

multi-value INSERT

A single INSERT statement that carries many VALUES rows, e.g. `INSERT INTO t ... VALUES (1,...),(2,...),...`. It amortizes SQL parsing, optimization, and a round trip across all rows in the batch.

max_allowed_packet

MySQL/MariaDB's cap on the size of a single SQL packet. Exceeding it triggers a `Packet too large` error, which is why unbounded batch size is risky.

rewriteBatchedStatements

A JDBC connection parameter for MySQL's driver. When enabled, consecutive `addBatch` statements are rewritten into multi-value INSERTs instead of sending each row separately, which is required to actually get batch performance in Java stacks.

From the discussion

Featured comments

卷土的土

Batch size also needs to be considered together with row width and lock time cost.

See top comments, translated →

Source: juejin.cn ↗ Google Translate ↗ Backup ↗