Insert MySQL into multiple tables with LAST_INSERT_ID ()

Question

I am trying to insert many users into a MySQL database with two tables:

The first table contains user data. An INSERT example is as follows ( id is the primary key, mail is the unique key):

 INSERT INTO users (id, mail, name) VALUES (NULL, " [e-mail] ", "John Smith") ON DUPLICATE KEY UPDATE name = VALUE(name) 

The second table contains the group to which the user belongs. It stores only two foreign keys users_id and groups_id . An example request is as follows:

 INSERT INTO users_groups (users_id, groups_id) VALUES (LAST_INSERT_ID(), 1) 

This setting works great for small datasets. When I import large amounts of data (> 1M rows), INSERT slows down. Obviously, it would be much better to do a batch insert:

 INSERT INTO users (id, mail, name) VALUES (NULL, " [e-mail] ", "John Smith"), (NULL, " [e-mail] ", "Anna Smith") ON DUPLICATE KEY UPDATE name = VALUE(name) 

and

 INSERT INTO users_groups (users_id, groups_id) VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 4) 

Of course, the problem is that LAST_INSERT_ID() returns only one (first) packet identifier INSERT .
So, I will need a “nested” INSERT package that IMO does not exist in MySQL.

What can be done to make INSERT faster?

Answer 1

Bulk inserts by default provide sequential automatic increments, with this knowledge you can do your inserts, for example:

 INSERT INTO users (id, mail, name) VALUES (NULL, " [e-mail] ", "John Smith"), (NULL, " [e-mail] ", "Anna Smith"), (...) # repeat n-times ; SET @LASTID=LAST_INSERT_ID() ; INSERT INTO users_groups (users_id, groups_id) VALUES (@LASTID - n , 1), # Note n in descending sequence (@LASTID - n-1, 1), ... (@LASTID - 1 , 1), (@LASTID - 0 , 4) ; 

For more information about bulk inserts and automatic adding, see http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

It is important to note that innodb_autoinc_lock_mode = 1

 show global variables like 'innodb_autoinc_lock_mode' 

Otherwise, consider wrapping your inserts in LOCK TABLES

 LOCK TABLES tbl_name WRITE ... sqls ... UNLOCK TABLES 

Answer 2

If you put millions of known rows in a table right away, consider using LOAD DATA INFILE , as it is designed for speed only in this type of scenario, as this quote from the docs attests to:

The LOAD DATA INFILE statement reads lines from a text file to a table at a very high speed.

And in Speed INSERT Statements :

When loading a table from a text file, use LOAD DATA INFILE . This is usually 20 times faster than using INSERT .

It is assumed that the source data comes from a text file or can be represented as a text file. If you have a group id in the file, you can do something like this:

 CREATE TEMPORARY TABLE load_users_groups ( mail VARCHAR(60), name VARCHAR(60), groupid INT, PRIMARY KEY (mail, name) ); LOAD DATA INFILE '/path/to/file.csv' INTO TABLE load_users_groups FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; -- use whatever optional syntax required to parse your file INSERT INTO users (mail, name) SELECT mail, name FROM load_users_groups ON DUPLICATE KEY UPDATE name = VALUES(name); INSERT INTO users_groups (users_id, groups_id) SELECT users.id, load_users_groups.groupid FROM users JOIN load_users_groups USING (mail, name); DROP TEMPORARY TABLE load_users_groups; 

Does this approach turn out faster than your current approach depends on whether you save more time with LOAD DATA INFILE than you spend on performing two additional INSERT ... SELECT to move the data to the desired tables. You can configure the keys on the temporary table; I cannot compare this for you based solely on the content of your question. I would be interested to know how this works.

The documentation also has a decent amount of tips for loading bulk data for InnoDB tables and loading bulk data for MyISAM tables . I will not dwell on them in detail, not least because you did not provide us with any information about the DDL or server, but you may find it useful to read this or the other in due time.

Answer 3

Had to deal with a similar problem.

MySQL really does not offer many ways to reliably reserve large batches of table identifiers for this purpose. I had a great half day, but to no avail. There are some hacks floating around, but nothing reflects my data.

I just made a user table marked “one by one” (better than a screw rather than a screw) and returned the newline id to my ORM. I have the id of the string I was working with, so I was able to throw it away and the data that should be imported into JSON, which holds them both together. This made it much easier for me to bulk insert and save data.

Best.

Answer 4

Links to How to insert many rows into a MySQL table and return new identifiers? http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id

Answer 5

Well, using java is easy ... you can use two for operators :). One of them is what does iterations of "wishBatchSize", and the other is a multiple of "wishBatchSize". For me it is very fast. 100 Mil records with PreparedStatement and Oracle in 140 minutes (22-table table).

  for (int m = 0; m < MULTIPLE_OF_BATCHSIZE; m++) { //batchSizeM * m + i => PK statement = conn.createStatement(); for (int i = 0; i < batchSizeM; i++) { StringBuilder sb = new StringBuilder(); sb.append("INSERT ... statement.addBatch(sb.toString()); } statement.executeBatch(); java.util.Date today = new java.util.Date(); System.out.println(m + " --- " + new java.sql.Timestamp(today.getTime())); conn.commit(); }