CODEWITHSUNDEEP
mysql
Cyril N.
Cyril N.

Reputation: 39919

Performance related issue on INSERT in MySQL

In general, when you want to include an foreign id in an entry, you do something like :

SELECT id FROM table_a WHERE key = :key LIMIT 1; then INSERT INTO table_b (table_a_id, misc) VALUES(:id, :misc);

(I try to be as generic as possible by not implementing language specific because it's not related to any programming language).

I'm wondering what is the difference in term of performance related to this :

INSERT INTO table_b (table_a_id, misc) VALUES((SELECT id FROM table_a WHERE key = :key LIMIT 1), :misc);

Simplified, I'm wondering if there is some kind of builtin optimisation mechanism in MySQL that detects the SELECT clause in the INSERT and replace it with the correct value one time, or if it does the SELECT for each INSERT.

Thank you for your input.

Upvotes: 1

Views: 46

Answers (2)

bishop
bishop

Reputation: 39494

No optimization is performed. MySQL evaluates the sub-query for each value set inserted. While MySQL may optimize sub-queries in a SELECT, sub-query expressions are not optimized according to the MySQL manual on INSERT and DML optimization.

Percona asked a similar question in the context of SELECT, and concluded that MySQL did not optimize it there. I ran a similar test in my environment using 5.6 and Percona's conclusion appears to also hold for INSERT.

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37129

If your ID has to be derived only once and used multiple times in an insert statement, write them separately like your first example suggests:

select id from table_a where key = :key limit 1;
insert into table_b (table_a_id, misc) values (:id, :misc);
insert into table_b (table_a_id, misc) values (:id, :misc2);
insert into table_b (table_a_id, misc) values (:id, :misc3);

If you are doing a one time insert with the selected ID, a one-liner does only one trip from caller to DB:

insert into table_b (table_a_id, misc)
select id, :misc from table_a where key = :key

You can also do insert in the way you wrote

insert into table_b (table_a_id, misc) values (
    (select id from table_a where key = :key limit 1),
    :misc
);

Upvotes: 1

Related Questions