CODEWITHSUNDEEP
mysqlsqlperformance
Daniel
Daniel

Reputation: 63

Why does my UPDATE query take so long when my SELECT query doesn't?

I am trying to do the following: let's say I have the following SELECT query (please excuse the german column names):

    SELECT  a1.aktivitäts_id, a1.herkunft_kontakt, a1.aktionsart,
            a1.aktionstyp, a2.aktivitäts_id, a2.herkunft_kontakt,
            a2.aktionsart, a2.aktionstyp, a2.datum
        FROM  Aktivitäten a1, Aktivitäten a2
        WHERE  a1.kunden_nr_aktivität = a2.kunden_nr_aktivität
          AND  a1.aktionsart = 'foo'
          AND  a2.herkunft_kontakt <> ''

This query takes about 4 seconds (the database has about 1 million records total) and returns about 400 records. However, when I want to UPDATE these same records with the following statement

    UPDATE  Aktivitäten a1, Aktivitäten a2
       SET a1.herkunft_kontakt = a2.herkunft_kontakt
        WHERE  a1.kunden_nr_aktivität = a2.kunden_nr_aktivität
          AND  a1.aktionsart = 'foo'
          AND  a2.herkunft_kontakt <> ''

The query always times out after taking forever. Am I doing something wrong or is this behaviour to be expected?

Upvotes: 1

Views: 125

Answers (2)

Rick James
Rick James

Reputation: 142298

Composite indexes that may help:

a1:  (aktionsart, kunden_nr_aktivit)   -- in this order
a2:  (kunden_nr_aktivit, herkunft_kontakt)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

First, learn to JOIN!!! Second, the difference is probably that you are updating too many rows -- and probably one row multiple times. I might suggest trying to aggregate before joining:

UPDATE Aktivitäten a1 JOIN
       (SELECT kunden_nr_aktivität, MAX(herkunft_kontakt) as herkunft_kontakt
        FROM Aktivitäten a2
        WHERE a2.herkunft_kontakt <> ''
        GROUP BY kunden_nr_aktivität
       ) a2
       USING (kunden_nr_aktivität)
     SET a1.herkunft_kontakt = a2.herkunft_kontakt 
WHERE a1.aktionsart = 'foo' ;

Upvotes: 2

Related Questions