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.
Best Answer
Maybe:
What is the datatype of
date
? If it isDATE
, you were excluding New Year's Day.I'm assuming that
id
is thePRIMARY 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 provideSHOW CREATE TABLE
andEXPLAIN UPDATE ...