0

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.

  • Table B is used for rows restriction only. Rewrite JOIN to WHERE EXISTS. This takes about 30 seconds to run How many rows there is in table B? This query updates the same 100 rows If so does the table B usage is needed really? using(id) In common id is primary key. Does it is not in your tables? – Akina Sep 1 '20 at 5:04
  • What is B for? To limit which items are to be set active? – Rick James Sep 2 '20 at 17:35
0

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 ...

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.