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.

Peltry

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. – Photo

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? – Peltry

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. – Photo

Answer 1

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.

Peltry

Answer 2

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

Nonattendance

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)); ? – Peltry

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

Answer 3

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

Diller