Boost MySQL Insert Speed: Batch Inserts, Transactions, and Ordered Data

For systems handling large volumes of data, database insert performance can become a bottleneck, especially in reporting systems where data loading may take hours.

1. Insert multiple rows in a single SQL statement

Instead of executing separate INSERT statements, combine rows into one statement:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

This reduces log volume, parsing overhead, and network I/O, leading to higher throughput.

2. Use transactions for batch inserts

Wrap a group of INSERTs in a transaction to avoid per‑statement transaction overhead:

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);
COMMIT;

The transaction ensures that all inserts are committed together, reducing the cost of creating and committing many small transactions.

3. Insert data in primary‑key order

When the primary key (e.g., a datetime column) is ordered, the B+Tree index can append entries efficiently, avoiding costly page splits and disk I/O.

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('2', 'userid_2', 'content_2', 2);

Tests comparing random versus ordered inserts (100, 1 000, 10 000, 100 000, 1 000 000 rows) show modest gains for ordered inserts.

Performance Summary

Combining batch inserts, transactions, and ordered data yields the best performance for large datasets. For small to medium sizes, batch + transaction improves speed noticeably. When the data volume exceeds the InnoDB buffer pool (≈10 million rows), batch‑only inserts degrade sharply, while batch + transaction + ordered inserts maintain good performance.

Practical Tips

SQL statements have a length limit; ensure combined statements stay below max_allowed_packet (default 1 MB, often increased to 8 MB for testing).

Transactions should not become too large; monitor innodb_log_buffer_size and commit before exceeding it to avoid disk flush penalties.

Below is a chart summarizing the test results: