CODEWITHSUNDEEP
sqldatabasemariadbquery-optimizationsql-insert
Marcel
Marcel

Reputation: 3258

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

I have a SQL like this:

INSERT INTO table1 (column1, column2) (
    SELECT column3, column4 FROM table2 WHERE column5 = 'value'
);

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?

Upvotes: 8

Views: 10910

Answers (6)

theking2
theking2

Reputation: 2813

There are a couple of additional things to try apart from the things already mentioned.

  • optimize the select by having an index on table2.column5 which you probably already have.
  • wrap the statement in a transaction, depending on the environment this might improve things.
  • disable foreign key constraints, if any are available .
  • and quite banal, programmably conditionally execute the statement if count(*) equals zero.
  • one other thing I'd try is add a DISTINCT in the select.

Upvotes: 0

Marcel
Marcel

Reputation: 3258

If I remember well, as a workaround I replaced theSELECT with a list of VALUES generated programmatically. Example:

INSERT INTO employees (employee_id, first_name, last_name, job_title)
VALUES
    (1, 'John', 'Doe', 'Software Engineer'),
    (2, 'Jane', 'Smith', 'Database Administrator'),
    (3, 'Bob', 'Johnson', 'Quality Assurance Analyst');

That runs really fast.

Unfortunately, for INSERT INTO SELECT, I never found a solution.

Upvotes: 0

Georg Richter
Georg Richter

Reputation: 7476

The reason for slowing down performance is quite simple. It's one of the few cases where InnoDB performs a lock on table t2 to prevent inconsistencies in (statement based) replication.

From the MySQL manual:

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.

Possible workarounds (see also Harrison Fisk's blog, written 14 years ago):

If you're using statement based replication, probably the only workaround would be

SELECT * FROM table2 INTO outfile 'tmp.txt';
LOAD DATA INFILE 'tmp.txt' INTO TABLE table1;

If you are using row based replication, set the transaction isolation level to READ COMMITTED.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Another (insecure) option would be to enable the server variable innodb_locks_unsafe_for_binlog

Upvotes: 6

SQLpro
SQLpro

Reputation: 5131

Can you try with this simple index :

CREATE INDEX SQLpro001 
   ON table2 (column5, column3, column4);

?

Try and publish the response time please...

Upvotes: 0

Ftoy
Ftoy

Reputation: 54

I have run into a similar problem was doing a complex query which retrieved over a 100k rows , The Select was relatively fast BUT the insert was extremely slow. I narrowed down the problem to the amount of rows being inserted 49k was the max for me that the insert was going fast but 50k rows took 30x the time to insert , I solved by splitting the query into multiple row queries. I would say this is an innodb problem (Some internal buffer probably going to disk instead of memory) slowing the insert considerably.

Upvotes: 2

joserobertog
joserobertog

Reputation: 119

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.

Upvotes: 1

Related Questions