9

When doing a bulk INSERT in InnoDB, should I use a transaction

START TRANSACTION;
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3);
INSERT INTO tbl_name (a,b,c) VALUES(4,5,6);
INSERT INTO tbl_name (a,b,c) VALUES(7,8,9);
COMMIT TRANSACTION;

Or combine multiple queries?

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

If it matters, I'm using PHP and the MySQL database is on the same machine.

Code
  • 6,041
  • 4
  • 35
  • 75

2 Answers2

8

I'd recommend combining multiple queries like you have in the bottom example.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

If either of the value-pair fails, none of the data will be inserted. This method also sends less characters and round-trip to the DB. The implication of less characters may not be that attractive but it still holds slight advantage.

EDIT:

Tim has a great question. Let me include information from MySQL doc

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • Hi Tim, I have added information about execution speed – zedfoxus Dec 24 '15 at 04:52
  • Interesting. It appears that this was not necessarily the case with SQL Server for some reason. So your recommendation then wins out in both cases. – Tim Biegeleisen Dec 24 '15 at 04:53
  • Shadow has a great point about `LOAD DATA INFILE`. Same link I posted indicates that `LOAD DATA INFILE` may be much faster than `INSERT` – zedfoxus Dec 24 '15 at 04:54
  • 2
    Just for future reference for myself, I'm including here an [answer from Martin Smith regarding SQL Server's 1000 inserts vs. insert with 1000 values](http://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values) – zedfoxus Dec 24 '15 at 05:03
  • This is exactly what I was reading, which prompted me to ask you for a clarification. Fortunately, MySQL behaves the way one would expect, with minimal weirdness. – Tim Biegeleisen Dec 24 '15 at 05:04
  • The link of "MySQL doc" is broken now. Please refer to https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html instead. – sky Mar 12 '21 at 06:58
1

The 1st version is technically not bulk insert, you are inserting 1 record at a time. That's the slowest possible method to import large amount of data.

The 2nd option is called bulk insert and is a lot faster.

However, you can use transactions with bulk insert as well.

The 3rd option is to load data with LOAD DATA INFILE command, which is even faster.

To speed up the insertion of massdata into innodb tables consider

  • turning off autocommit mode
  • turning off unique key checks
  • turning off foreignkey checks

See mysql documentation on these

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Great point about `LOAD DATA INFILE` - http://dev.mysql.com/doc/refman/5.7/en/insert-speed.html indicates this method as a very fast method for inserts. – zedfoxus Dec 24 '15 at 04:55
  • I'm working with a 30 millions rows database, and doing massive data import with LOAD DATA I experimented unsuccesfully the "autocommit=0" method. The fastest method for me was to DROP all indexes, do the LOAD DATA and then rebuild indexes. – decadenza Apr 15 '16 at 16:14
  • SQL Server likewise tends to be faster if you drop and rebuild the indexes. – Jonathan Allen Dec 26 '19 at 22:13
  • @JonathanAllen mysql manual refers to multi-rows inserts as bulk inserts. MS SQL documentation may refer to the same as batch inserts, but since this question is about mysql, I'll follow the mysql terminology. – Shadow Dec 26 '19 at 23:26