Single INSERT command of multiple rows vs. multiple INSERT commands of single rows

Question

I would like to know which solution is faster than the other. So, imagine we'd like to INSERT 10000 rows into a table. We have the following 2 solutions:

Solution 1: Run the INSERT query 10000 times:

INSERT INTO myTable (a,b,c) VALUES ("a","b","c"); x 10000

Solution 2: Run one INSERT query with 10000 rows at once:

INSERT INTO myTable (a,b,c) VALUES 
("a","b","c"),
("a","b","c"),
("a","b","c"),
 ...,
("a","b","c");

Please check the following link: http://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert — Nedim Hadžiosmanović, Feb 25 '15 at 10:23

score 1 · Answer 1 · answered Feb 25 '15 at 10:18

1

Solution 2 : it need just one connection to the database, the solution 1 need 1000 connections and it's so mush.

answered Feb 25 '15 at 10:18

Zakaria.dem

293
5
10

What if we run all 10000 `INSERT`s with one connection? 1. open connection; 2. perform 10000 `INSERT`s ; 3. close connection – disasterkid Feb 25 '15 at 10:20
you can do for exemple a loop and insert 1000 by 1000 – Zakaria.dem Sep 18 '16 at 17:42

score 1 · Answer 2 · answered Feb 25 '15 at 10:24

1

Solution 1 is around 10 times slower than solution 2.

From the guy with the mysql database server with 100gb data and 5000 request per second.

answered Feb 25 '15 at 10:24

Gleiemeister 2000

731
4
9
23

Single INSERT command of multiple rows vs. multiple INSERT commands of single rows

2 Answers2