I have a MySQL InnoDB database with two tables related by a foreign key. Each time a row is inserted into one it uses an INSERT ... SELECT
statement from the other to get the corresponding foreign key.
I have an application that is continuously doing a large number of these INSERT ... SELECT
statements to populate this database. I have read that using a multiple row insert, i.e. INSERT INTO table (col1, col2, col3) VALUES (1,2,3), (4,5,6), ...
, can be significantly faster than individual inserts. Unfortunately it appears that splitting the database into separate tables prevents me from taking advantage of this? Is there a workaround or another way to improve the speed of these inserts?
EDIT:
Would any of the following be likely to help?
- Combining the
INSERT ... SELECT
statements with aUNION
. - A multiple row insert into a temporary table followed by a
JOIN
and a singleINSERT ... SELECT
. - Splitting the inserts among multiple threads.