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