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

Question

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?

Could you please explain what you try to achieve with this update? Now it looks like CROSS JOIN — Sergey, Mar 16 '21 at 14:37
Does this answer your question? [mysql update column with value from another table](https://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table) — Adrian J. Moreno, Mar 16 '21 at 14:53
Where and when did you learn comma-separated joins? The have been obsolete for almost three decades now. Use explicit joins (e.g. `INNER JOIN`), which have been introduced in the SQL standard in 1992. — Thorsten Kettner, Mar 16 '21 at 20:15
From your update statement I conclude that there is sometimes one row per `kunden_nr_aktivität` with a `herkunft_kontakt`, sometimes no such row, and never more than one such row. You only want to update rows with `aktionsart = 'foo'` where exists such a row for the `kunden_nr_aktivität`. Is this correct? If not then your updates statement is inappropriate. — Thorsten Kettner, Mar 16 '21 at 20:18

score 2 · Accepted Answer · answered Mar 16 '21 at 14:38

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' ;

Exactly what I wanted to achieve! Thank you very much and I will keep the thing about joining explicitly in mind :). — Daniel, Mar 17 '21 at 08:23

score 0 · Answer 2 · answered Mar 16 '21 at 20:09

0

Composite indexes that may help:

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

answered Mar 16 '21 at 20:09

Rick James

135,179
13
127
222

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

2 Answers2