I have two queries that run far too slow and I have no idea why.
Both tables contain about 500000 rows. Each call needs ~20 minutes and the they have a really high CPU usage (~90% on Mac).
I have tested the queries with MAMP MySQL (InnoDB).
UPDATE foo_new as new
JOIN foo_active as active
ON active.id_3=new.id_3
SET active.norf=new.norf,
active.time_left=new.time_left
-
INSERT INTO foo_active (id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved)
SELECT id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved
FROM foo_new new
WHERE NOT EXISTS (
SELECT id_a, id_b, id_c, norf, defg, quantity, time_left, date_saved
FROM foo_active active
WHERE new.id_c = active.id_c
)
Both tables have the same structure: