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, Apr 09 '12 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, Apr 09 '12 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, Apr 09 '12 at 15:45

eabraham · Answer 1 · 2012-04-09T17:44:41.230

13

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', 'me@somewhere.com', 1);

edited Apr 09 '12 at 17:44

answered Apr 09 '12 at 16:43

eabraham

4,094
1
23
29

1

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', 'me@somewhere.com', 1), ('Bob', 'Smith', 'me@s.com', 2), ('Sally', 'Wood', 'me@s.com',2), ('Will', 'Black','me@s.com', 3)); ? – Michael Franze Apr 09 '12 at 17:15
1

No, whatever language you are using should call the SP each time it is iterating through the array. – eabraham Apr 09 '12 at 17:50

score 2 · Accepted Answer · edited May 23 '17 at 11:58

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.

score 0 · Answer 3 · answered May 12 '22 at 20:18

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

Using Mysql to do multiple INSERT on linked tables

3 Answers3

Linked