INSERT INTO SELECT is very slow, but INSERT or SELECT are fast when running separetely

user_5411494 0
Viewed 1528

I have a SQL like this:

INSERT INTO table1 (column1, column2) (
    SELECT column3, column4 FROM table2 WHERE column5 = 'value'
);
  • The table1 has 3,500,000 rows.
  • The table2 has 900,000 rows.
  • SELECT column3, column4 FROM table2 WHERE column5 = 'value' returns NO registers (ZERO) and takes ~0.004 seconds.
  • INSERT INTO table1 (column1, column2) VALUES ('value', 'value') takes ~0.004 seconds as well.

But when I combine both in an INSERT INTO SELECT statement, as shown above, it takes ~7.7 seconds. Is there an explanation? And is there a solution?

mariadb sql-insert query-optimization sql database
1 Answers

Sometimes the performance is slower when you execute an INSERT with SELECT because the type of fields in the origin table are diferents from de destiny table, so when the INSERT-SELECT is executing there is an implicit cast on the values from the SELECT so them could be persisted in the destiny table. That implicit cast is not made with the best way of performance and is slower sometimes from a direct insert from values.

user_158872700
Related