1

Possible Duplicate:
Which is faster: multiple single INSERTs or one multiple-row INSERT?

While going through a book on mysql, I found out two ways to insert a row in a database.

Method 1 

INSERT INTO tableName (col1, col2, col3) VALUES('a', 'b', 'c');
INSERT INTO tableName (col1, col2, col3) VALUES('d', 'b', 'c');
INSERT INTO tableName (col1, col2, col3) VALUES('e', 'b', 'c');

Method 2

INSERT INTO tableName (col1, col2, col3) VALUES('a', 'b', 'c'), ('d', 'b', 'c'), ('e', 'b', 'c');

Is the second method more efficient than the first one ? Or does it simply calls the Method 1 multiple times ?

Community
  • 1
  • 1
Prashant Singh
  • 3,725
  • 12
  • 62
  • 106
  • [Read this article](http://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert) – John Woo Oct 26 '12 at 14:06

1 Answers1

2

The second is more efficient.

The first method creates a connection each time you want to insert a row, where the second uses a single connection to insert all your rows. There is, however, a max_allowed_packet that limits the length of the client's INSERT statement.

Kermit
  • 33,827
  • 13
  • 85
  • 121
  • Can you give a brief idea abt the size of max_allowed_packet? Is it user configurable ? – Prashant Singh Oct 26 '12 at 14:08
  • @PrashantSingh [This](http://superuser.com/a/273833) topic explains what it controls and [this](http://stackoverflow.com/a/5688506/679449) topic explains how to configure the setting. – Kermit Oct 26 '12 at 14:11