MySQL – Troubleshooting Slow INSERT and UPDATE Queries

MySQLmysql-5.5

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:

enter image description here

Best Answer

Database Indexes are a major benefit to performance of queries. I work almost exclusively with Microsoft SQL Server, but the principles apply on pretty much all flavors of SQL servers.

Because the indexes are smaller than the overall table and have the needed value to be searched in a sorted order, creating indexes (to a reasonable degree) is the quickest way to speed up queries.

You can overdo it, of course, since too many indexes can drag down performance.

Related Question