Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using Mysql to do multiple INSERT on linked tables

I have two tables, one linked to the Primary Key of the other. At the moment I INSERT into table A, get the LAST_INSERT_ID, and then INSERT into table B.

But I have hundreds of records to insert and I want to speed things up.

In Mysql you can either:

INSERT INTO table_a (v1, v2, c3) VALUE (0, 1, 2);

INSERT INTO table_a (v1, v2, v3) VALUE (4, 5, 6); 

etc, or

INSERT INTO table_a (v1, v2, v3) VALUE (0, 1, 2), (4, 5, 6), etc to add multiple entries faster - but only for one table.

Of course the latter is much faster. I was wondering whether it was possible to replicate this behaviour for my example with two linked tables using a Stored Procedure, and whether it would have a similarly dramatic improvement in performance:

something like: call special_insert((0, 1, 2), (4, 5, 6), etc); or similar.

I have no Stored Procedure experience, so I'm fishing for ideas on which direction to proceed in.

like image 871
Michael Franze Avatar asked Apr 09 '12 15:04

Michael Franze


2 Answers

Here is an example of a store procedure with a two table insert including Last_Insert_ID().

DELIMITER //
CREATE PROCEDURE new_person(
  first CHAR(35), last CHAR(35), email CHAR(255), tool_id INT)
BEGIN
START TRANSACTION;
   INSERT INTO person(firstname, lastname, email) 
     VALUES(first, last, email);

   INSERT INTO tasks (engineer_id, tool_id) 
     VALUES(LAST_INSERT_ID(), tool_id);
COMMIT;
END//
DELIMITER ;

CALL new_person('Jerry', 'Fernholz', '[email protected]', 1);
like image 193
eabraham Avatar answered Oct 24 '22 17:10

eabraham


After some further investigation it appears as if SP would not offer significant speed improvements and cannot accept bulk parameters like INSERT INTO

MySQL Stored Procedure vs. complex query

But I still needed to insert a fairly large number of linked records in one so I did the following:

INSERT INTO a (x, y) VALUES (1,2), (3,4), (5,6), ... (N-1, N)

id = GET_LAST INSERT_ID

ids range from id to id+N as long as we use InnoDB tables:

MySQL LAST_INSERT_ID() used with multiple records INSERT statement

MySQL LAST_INSERT_ID() used with multiple records INSERT statement

http://gtowey.blogspot.com/2012/02/multi-insert-and-lastinsertid.html

and then

INSERT INTO b (a_id, z) VALUES (id,2), (id+1,4), (id+2,6), ... (id+N, 11) only gotcha is you need to know your mysql increment increment from replication.

like image 42
Michael Franze Avatar answered Oct 24 '22 19:10

Michael Franze