Using Mysql to do multiple INSERT on linked tables

Question

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.

The SP would let you simulate a multi-table monolithic insert. The question would be... how could you separate the table A data from the table B data in the parameters list. — Marc B
– Marc B, Commented Apr 9, 2012 at 15:23
Well, by writing my own Stored Procedure I would know what the data in the parameter list would mean... Would the SP be significantly faster than individual INSERTS? — Michael Franze
– Michael Franze, Commented Apr 9, 2012 at 15:32
I don't know about significantly. You'd have to benchmark both methods, but in the end you'd still be doing two inserts and can't get around that fact. — Marc B
– Marc B, Commented Apr 9, 2012 at 15:45

eabraham · Accepted Answer · 2012-04-09 17:44:41Z

14

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);

edited Apr 9, 2012 at 17:44

answered Apr 9, 2012 at 16:43

eabraham

4,1641 gold badge25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Franze Over a year ago

that's a very succinct SP example, thanks, I appreciate that. Would it be possible to pass an array, such as: CALL new_person(('Jerry', 'Fernholz', '[email protected]', 1), ('Bob', 'Smith', '[email protected]', 2), ('Sally', 'Wood', '[email protected]',2), ('Will', 'Black','[email protected]', 3)); ?

eabraham Over a year ago

No, whatever language you are using should call the SP each time it is iterating through the array.

Community · Accepted Answer · 2017-05-23 11:58:51Z

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

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.

Bơ Loong A Nhứi · Accepted Answer · 2022-05-12 20:18:10Z

Here is MySql stored procedure, we can create thousand of records as we defined.

drop procedure batchInsertUser;
call batchInsertUser(2);

DELIMITER ;;
CREATE PROCEDURE batchInsertUser(totalRecs int)
BEGIN
  SET @createdRecs = 0;
  SET @maxDataId = (SELECT MAX(id) FROM userTable);
  WHILE @createdRecs < totalRecs DO
    SET @createdRecs = @createdRecs + 1;
    SET @maxDataId = @maxDataId + 1;
    SET @userName = CONCAT('batch', @maxDataId );
    INSERT INTO userTable (id, username, created_at, updated_at)
      VALUES (@maxUserId,  @userName, now(), now());
    INSERT INTO userProfile
      (user_id, gender, birthday, created_at, updated_at)
      VALUES
      (@maxUserId, @userName, 'F', '1999-01-10', now(), now());
END ;;

Collectives™ on Stack Overflow

Using Mysql to do multiple INSERT on linked tables

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related