How Fast Can MySQL Write? Comparing Single-Row, Batch, and Multi-Row Inserts

Test Preparation

A local MySQL instance with default parameters is started, and a table named user is created:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'User ID',
  `name` varchar(32) DEFAULT 'FunTester' COMMENT 'User name',
  `age` int NOT NULL COMMENT 'User age',
  `level` int DEFAULT NULL COMMENT 'User level',
  `region` varchar(32) DEFAULT '小八超市' COMMENT 'User region',
  `address` varchar(512) DEFAULT NULL COMMENT 'User address',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

To measure throughput, the following query counts rows per second:

SELECT COUNT(*), create_time FROM user GROUP BY create_time;

After each test run the table is cleared:

TRUNCATE TABLE user;

While‑Loop Insertion

This method uses a single‑threaded JDBC while(true) loop that generates random data and executes an INSERT for each row.

package com.funtest.mysql;
import com.funtester.db.mysql.FunMySql;
import com.funtester.frame.SourceCode;
import com.funtester.utils.StringUtil;
/**
 * Write data to MySQL via JDBC in a while loop
 */
class MysqlWriteWhile extends SourceCode {
    public static void main(String[] args) {
        String sqlFormat = "insert into user (name, age, level, region, address) values (?,?,?,?,?)";
        String ipPort = "127.0.0.1:3306";
        String database = "funtester";
        String user = "root";
        String password = "funtester";
        def base = new FunMySql(ipPort, database, user, password);
        def preparedStatement = base.connection.prepareStatement(sqlFormat);
        while (true) {
            String name = StringUtil.getString(16);
            int age = getRandomInt(100);
            int level = getRandomInt(10);
            String region = StringUtil.getString(32);
            String address = StringUtil.getString(512);
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setInt(3, level);
            preparedStatement.setString(4, region);
            preparedStatement.setString(5, address);
            preparedStatement.executeUpdate();
        }
        preparedStatement.close();
        base.close();
    }
}

Measured rows per minute (averaged over the first 10 seconds) range from 6,000 to 7,400, with an overall throughput of roughly 7 k rows per minute.

0 s: 6,374 rows

1 s: 6,197 rows

2 s: 6,156 rows

3 s: 6,176 rows

4 s: 6,332 rows

5 s: 6,545 rows

6 s: 7,088 rows

7 s: 7,309 rows

8 s: 7,408 rows

9 s: 6,099 rows

JDBC Batch Insertion

This variant builds a batch of 10 rows per loop iteration and executes the batch with executeBatch(). The code is otherwise similar to the while‑loop version.

package com.funtest.mysql;
import com.funtester.db.mysql.FunMySql;
import com.funtester.frame.SourceCode;
import com.funtester.utils.StringUtil;
/**
 * Write data to MySQL via JDBC batch processing
 */
class MysqlWriteBatch extends SourceCode {
    public static void main(String[] args) {
        String sqlFormat = "insert into user (name, age, level, region, address) values (?,?,?,?,?)";
        String ipPort = "127.0.0.1:3306";
        String database = "funtester";
        String user = "root";
        String password = "funtester";
        def base = new FunMySql(ipPort, database, user, password);
        def preparedStatement = base.connection.prepareStatement(sqlFormat);
        while (true) {
            for (int j = 0; j < 10; j++) {
                String name = StringUtil.getString(16);
                int age = getRandomInt(100);
                int level = getRandomInt(10);
                String region = StringUtil.getString(32);
                String address = StringUtil.getString(512);
                preparedStatement.setString(1, name);
                preparedStatement.setInt(2, age);
                preparedStatement.setInt(3, level);
                preparedStatement.setString(4, region);
                preparedStatement.setString(5, address);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
        }
        preparedStatement.close();
        base.close();
    }
}

Results (rows per minute) are comparable to the simple while loop, ranging from about 6,600 to 7,300 rows:

17 s: 7,308 rows

18 s: 6,817 rows

19 s: 6,871 rows

20 s: 6,367 rows

21 s: 6,631 rows

22 s: 7,310 rows

23 s: 6,903 rows

24 s: 7,258 rows

25 s: 7,180 rows

26 s: 7,309 rows

27 s: 7,208 rows

28 s: 6,640 rows

The batch approach mainly reduces network round‑trips; it does not significantly change the server‑side processing time.

Multi‑Row INSERT

Here a single INSERT statement contains many value tuples (N rows per statement). This differs from batch processing, which sends many separate statements in one round‑trip.

Measured rows per minute show a higher but unstable throughput:

28 s: 12,360 rows

29 s: 11,460 rows

30 s: 14,800 rows

31 s: 22,110 rows

32 s: 23,950 rows

33 s: 24,750 rows (peak)

34 s: 24,030 rows

35 s: 15,230 rows

Performance improves up to roughly 24 k rows per minute, but the variation can be as large as a factor of two, indicating instability under the single‑thread test.

In summary, the three single‑thread insertion techniques achieve the following approximate throughputs on a default MySQL instance: while‑loop and JDBC batch around 7 k rows per minute, and multi‑row INSERT up to 24 k rows per minute with noticeable variance. Extending these methods to multi‑threaded execution and integrating a dedicated performance‑testing framework would likely yield higher and more consistent write rates.