MySQL - INSERT INTO table SELECT WHERE field IN (subquery) - Use of IN make things extremely slow, correct way to do it?

Question

I have a query that inserts data in a table, data is retrieved from another table, the WHERE condition specify all the records in the first table:

INSERT INTO `table_a` (`field_x`, `field_y`)
SELECT `field_x`, `field_y`
FROM `table_b`
WHERE `field_x` IN
(
   SELECT `field_x`
   FROM `table_a`
) ON DUPLICATE KEY UPDATE `field_y`=`table_b`.`field_y`, `field_x`=`table_b`.`field_x`

Pratically, for each field_x of table_a I want to select all field_x and field_y from table_b and then update table_a by inserting all the returned rows; I already know that the use of IN with subquery is known to be slow; I'm not sure this is the right way, considering it is also ultra-slow, and I'm also asking myself if this approach isn't totally wrong.. Are there any SQL experts here who can help and address me to the right approach to the problem?

do your tables have the appropriate indexes? – Barranka May 29 '15 at 22:36 — Barranka, May 29 '15 at 22:36

wiretext · Answer 1 · 2015-05-29T19:43:29.670

0

INSERT INTO `table_a` (`field_x`, `field_y`)
SELECT `table_b`.`field_x`, `table_b`.`field_y`
FROM `table_a`
join `table_b`
on DUPLICATE KEY UPDATE `table_a`.`field_y`=`table_b`.`field_y`

edited May 29 '15 at 19:43

answered May 29 '15 at 12:11

wiretext

3,302
14
19

..I mean, it works, but still very slow.. 20+ seconds.. any idea? – user3821180 May 29 '15 at 14:09

MySQL - INSERT INTO table SELECT WHERE field IN (subquery) - Use of IN make things extremely slow, correct way to do it?

1 Answers1