Database : beware when using “INSERT INFO…SELECT” in MySQL

If you don’t know, you should read this. Even you know, you should read it.

When you run this sql:

INSERT INTO table1 SELECT * FROM table2

If it does not finish yet and you run this sql:

INSERT INTO table2 ....

You will have a problem that it is slow. It will do nothing until the first sql is done. That is because “INSERT INFO … SELECT FROM” locks the table2.

You may already know this thing.

But there is another thing. Assume that you have table3 and it has a foreign key linking to the primary key in table2. When you insert in to table3 with a value for that foreign key, you will have the same problem too.

So, it can have a scenario that you wonder why a simple INSERT talking time. You cannot find anywhere SQL locking on that table. Which, actually, the cause of the problem is there is a mystery job running INSERT INFO on another table which your target table has a foreign key to.

For in here:

https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

https://www.percona.com/blog/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/