0

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
mcruz
  • 1,534
  • 2
  • 11
  • 14
Jaeyoung Lee
  • 258
  • 1
  • 2
  • 8
  • Look at insert_batch or multi_query() functions. also: http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql – Sami May 31 '16 at 23:21

2 Answers2

4

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
1

It would be helpful to know you are generating that list of words but you could do insert into table (column) values (word), (word2);

Without more info that is about as much as we can help

You could add a loop in whatever language is needed to iterate over the list to add them.

happymacarts
  • 2,547
  • 1
  • 25
  • 33