Boost MySQL Insert Speed: 4 Proven Techniques Tested

Why Optimize MySQL Inserts?

Large‑scale systems often suffer from slow query performance and lengthy data‑loading times, especially in reporting pipelines where import jobs can take hours. Improving insert efficiency is therefore crucial.

1. Insert Multiple Rows with a Single SQL Statement

Typical separate inserts:

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);

Combined batch insert:

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

Batching reduces the amount of binary log and InnoDB transaction log written to disk, lowers flush frequency, cuts parsing overhead, and saves network I/O.

2. Wrap Inserts in a Transaction

Using an explicit transaction:

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;

All rows are committed together, eliminating the overhead of creating a separate transaction for each INSERT and reducing disk writes.

3. Insert Data in Primary‑Key Order

Unordered inserts (random primary‑key values) increase index‑maintenance cost because InnoDB must split and merge B‑tree pages. Ordered inserts keep new rows at the end of the index, allowing fast sequential writes.

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);

4. Comprehensive Performance Test

Combining batch inserts, transactions, and ordered data yields the best results for large volumes. For small datasets, batch‑plus‑transaction gives a clear boost, but performance degrades sharply beyond 10 million rows due to InnoDB buffer limits. Adding ordered inserts maintains high throughput even at tens of millions of rows.

Important Considerations

SQL statement length

is limited (default 1 MiB, configurable via max_allowed_packet); ensure batch statements stay within this limit. Transaction size matters; large transactions may exceed innodb_log_buffer_size and cause disk flushes, so commit before reaching that threshold.

Original article: http://n.wz2.in/110 – Author: BohrTang