mysql multiple-row insert, single-row insert, or transaction insert


http://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert

"""...
Which is faster: multiple single INSERTs or one multiple-row INSERT?

...
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.

...
in general, multiple inserts will be slower because of the connection overhead. Doing multiple inserts at once will reduce the cost of overhead per insert.


..."""


INSERT INTO tablename (col1, col2) VALUES (1, 2), (3, 4), (5, 6), (7, 8)
Which will insert 4 rows.



//=== https://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
"""...
The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

Connecting: (3)

Sending query to server: (2)

Parsing query: (2)

Inserting row: (1 × size of row)

Inserting indexes: (1 × number of indexes)

Closing: (1)
..."""



//=== https://github.com/felixge/node-mysql/
"""...
connection.beginTransaction(function(err) {
if (err) { throw err; }
connection.query('INSERT INTO posts SET title=?', title, function(err, result) {
if (err) {
connection.rollback(function() {
throw err;
});
}

var log = 'Post ' + result.insertId + ' added';

connection.query('INSERT INTO log SET data=?', log, function(err, result) {
if (err) {
connection.rollback(function() {
throw err;
});
}
connection.commit(function(err) {
if (err) {
connection.rollback(function() {
throw err;
});


.."""