Simple Update Join much slower than it should be (MYSQL)

Question

This is a simple Update Join that updates only about 100 rows:

Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020'

This takes about 30 seconds to run, despite the fact that:

• This query updates the same 100 rows and takes milliseconds to run:

  Update A SET active = 1 WHERE date > '2020'

• The join condition is fast, this query does the same join and takes less than a second

  SELECT * FROM A INNER JOIN B using(id) WHERE A.date > '2020'

• The field active not part of any index

• Table A has an index on (id, date), and table B has an index on id.

I tried putting the where condition in the join (using on date > '2020') but it didn't help. I'm absolutely stumped why this takes so long. Any help is appreciated.

Answer 1

Maybe:

UPDATE A
    SET A.active = 1
    WHERE A.date >= '2020-01-01'
      AND ( EXISTS ( SELECT 1 FROM B WHERE B.id = A.id ) )

What is the datatype of date? If it is DATE, you were excluding New Year's Day.

I'm assuming that id is the PRIMARY KEY if each table. But, given that, I must ask why you have two tables with the same PK -- That is usually poor schema design. (There are exceptions.) Furthermore, INDEX(id, date) is probably useless.

Needed: A: INDEX(date). Even so, that might be ignored, depending on the data distribution. Please provide SHOW CREATE TABLE and EXPLAIN UPDATE ...