Search code examples
mysqlsqlnode.jsdatabasedbconnection

What is the best way to saving many rows to DB at the same time?

I have some words like ["happy","bad","terrible","awesome","happy","happy","horrible",.....,"love"]. These words are large in number, exceeding 100 ~ 200 maybe.

I want to saving that to DB at the same time. I think calling to DB connection at every word is so wasteful.

What is the best way to save?

table structure
wordId userId word
Solution

  • You are right that executing repeated INSERT statements to insert rows one at a time i.e processing RBAR (row by agonizing row) can be expensive, and excruciatingly slow, in MySQL.

    Assuming that you are inserting the string values ("words") into a column in a table, and each word will be inserted as a new row in the table... (and that's a whole lot of assumptions there...)

    For example, a table like this:

     CREATE TABLE mytable (mycol VARCHAR(50) NOT NULL PRIMARY KEY) ENGINE=InnoDB

    You are right that running a separate INSERT statement for each row is expensive. MySQL provides an extension to the INSERT statement syntax which allows multiple rows to be inserted.

    For example, this sequence: 

    INSERT IGNORE INTO mytable (mycol) VALUES ('happy');
INSERT IGNORE INTO mytable (mycol) VALUES ('bad');
INSERT IGNORE INTO mytable (mycol) VALUES ('terrible');

    Can be emulated with single INSERT statement

    INSERT IGNORE INTO mytable (mycol) VALUES ('happy'),('bad'),('terrible');

    Each "row" to be inserted is enclosed in parens, just as it is in the regular INSERT statement. The trick is the comma separator between the rows.

    The trouble with this comes in when there are constraint violations; either the whole statement succeeds or fails. Unlike the individual inserts, where one of them can fail and the other two succeed.

    Also, be careful that the size (in bytes) of the statement does not exceed the max_allowed_packet variable setting.

    Alternatively, a LOAD DATA statement is an even faster way to load rows into a table. But for a couple of hundred rows, it's not really going to be much faster. (If you were loading thousands and thousands of rows, the LOAD DATA statement could potentially be much faster.