1

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

  • 1
    From the timing it sounds like you might be scanning the whole data set. Have you created indexes that would benefit these queries? e.g. foo_new.id_c and foo_active.id_c – RLF Dec 10 '14 at 16:59
  • @RLF Thank you so much. It does need just 3 seconds now. :) – FadeOut Dec 10 '14 at 17:10
0

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.