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?
Would any of the following be likely to help?
- Combining the
INSERT ... SELECTstatements with a
- A multiple row insert into a temporary table followed by a
JOINand a single
INSERT ... SELECT.
- Splitting the inserts among multiple threads.