MySQL Insert Performance: Batching, Transactions and Indexes

Inserting a million rows can take seconds or hours, depending on how you do it. These are the steps that matter, in the order that matters.

1. Do not commit every row

With autocommit on (the default), each statement is its own transaction, and InnoDB flushes its log to disk at every commit. That flush is the slowest part.

query.sql

SQL

CREATE TABLE readings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sensor_id INT NOT NULL,
    value DECIMAL(8,2) NOT NULL,
    recorded_at DATETIME NOT NULL
);

SET autocommit = 0;
INSERT INTO readings (sensor_id, value, recorded_at) VALUES (1, 20.5, '2026-07-01 10:00:00');
INSERT INTO readings (sensor_id, value, recorded_at) VALUES (1, 20.7, '2026-07-01 10:01:00');
INSERT INTO readings (sensor_id, value, recorded_at) VALUES (2, 18.1, '2026-07-01 10:00:00');
COMMIT;
SET autocommit = 1;
SELECT COUNT(*) AS rows_loaded FROM readings;

Output

OUTPUT

+-------------+
| rows_loaded |
+-------------+
|           3 |
+-------------+
1 row in set

Wrapping many inserts in one transaction (START TRANSACTION ... COMMIT) pays the flush cost once instead of once per row.

2. Use multi-row statements

500 to 5,000 rows per INSERT is a good range (Inserting Multiple Rows).

3. Use LOAD DATA for very large files

For hundreds of thousands of rows or more, LOAD DATA is the fastest (LOAD DATA).

4. Keep indexes lean

Every index must be updated for every row you insert. A table with six indexes does about six times the index work. Before a one-time bulk load into an empty table, you can:

Create the table without secondary indexes, load the data, then add the indexes with ALTER TABLE ... ADD INDEX. Building an index once on all the data is faster than updating it a million times.
Keep the primary key.

query.sql

SQL

CREATE TABLE readings_fast (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sensor_id INT NOT NULL,
    value DECIMAL(8,2) NOT NULL,
    recorded_at DATETIME NOT NULL
);
INSERT INTO readings_fast (sensor_id, value, recorded_at) SELECT sensor_id, value, recorded_at FROM readings;
ALTER TABLE readings_fast ADD INDEX idx_sensor_time (sensor_id, recorded_at);
SHOW INDEX FROM readings_fast;

Output

OUTPUT

+---------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table         | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| readings_fast |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| readings_fast |          1 | idx_sensor_time |            1 | sensor_id   | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| readings_fast |          1 | idx_sensor_time |            2 | recorded_at | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set

5. Insert in primary key order

InnoDB stores rows in primary key order. Inserting in key order (an AUTO_INCREMENT key does this for you) keeps writes sequential. Random keys, such as UUID v4, cause page splits and slow inserts (UUIDs as Keys).

6. Think about foreign keys and triggers

Each foreign key does a lookup in the parent table on every insert. Each trigger runs for every row. For a big one-time load, you can disable foreign key checks for the session, if you are sure the data is consistent, and re-enable them afterwards:

query.sql

SQL

1
2
3

SET foreign_key_checks = 0;
-- ...load the data...
SET foreign_key_checks = 1;

Do this only for a controlled import. Do not leave it off, and do not do it in application code.

7. Server settings (for administrators)

Setting	Effect
`innodb_flush_log_at_trx_commit`	`1` (default) is safest; `2` is faster, with a small risk of losing about one second of commits if the OS crashes
`innodb_buffer_pool_size`	Memory for data and indexes. Bigger is better, up to about 70 percent of RAM on a dedicated server
`innodb_redo_log_capacity` (8.0.30 and later)	Size of the redo log. Bigger helps write-heavy loads
`max_allowed_packet`	Allows larger batches

Do not change durability settings casually. They are trade-offs, not free speed.

8. Measure

Test on realistic data and time it:

query.sql

SQL

SELECT 'Time your own import with 100, 1000 and 5000 rows per statement' AS advice;

Output

OUTPUT

+-----------------------------------------------------------------+
| advice                                                          |
+-----------------------------------------------------------------+
| Time your own import with 100, 1000 and 5000 rows per statement |
+-----------------------------------------------------------------+
1 row in set

Use SET profiling (deprecated), the slow query log or simply your application’s timer, and change one thing at a time.

Quick summary

Technique	Typical gain
One transaction instead of autocommit per row	Large
Multi-row `INSERT`	Large
`LOAD DATA`	Largest
Add secondary indexes after loading	Medium to large
Sequential primary keys	Medium on big tables
Foreign keys off for a one-time load	Medium, with risk

lightbulb

The first fix usually works

Most “slow insert” complaints are solved by step 1 and 2: stop committing every row, and send rows in batches.