Update query execute very slow on mysql when more than 1 million records

Question

Query is taking more than one hour.

Anyone can help with this? thanks in advance.

My query is

UPDATE coupons SET expiration_date='2020-06-06'

My table structure

+-----------------------+--------------------------------------------+------+-----+---------+----------------+
| Field                 | Type                                       | Null | Key | Default | Extra          |
+-----------------------+--------------------------------------------+------+-----+---------+----------------+
| id                    | int(11) unsigned                           | NO   | PRI | NULL    | auto_increment |
| vendor_id             | int(11)                                    | NO   | MUL | NULL    |                |
| type                  | enum('food','shopping','home','lifestyle') | NO   |     | NULL    |                |
| title                 | varchar(255)                               | NO   | MUL | NULL    |                |
| slug                  | varchar(255)                               | YES  |     | NULL    |                |
| thumbnail_image       | varchar(600)                               | YES  |     | NULL    |                |
| coupon_name           | varchar(255)                               | NO   |     | NULL    |                |
| expiration_date       | date                                       | YES  | MUL | NULL    |                |
| discount_type         | enum('d','p')                              | YES  |     | NULL    |                |
| discount_rate         | double(7,2)                                | YES  |     | NULL    |                |
| code_type             | enum('i','c','u','b')                      | NO   |     | NULL    |                |
| code                  | text                                       | YES  |     | NULL    |                
| created_at            | datetime                                   | YES  |     | NULL    |                |
| updated_at            | datetime                                   | YES  |     | NULL    |                
+-----------------------+--------------------------------------------+------+-----+---------+----------------+

This update has no `WHERE` clause, and targets the entire table. Therefore, there is no real way to improve upon the execution plan already being used. — Tim Biegeleisen, Jan 18 '20 at 11:39
basically, it will update all your records because you did not provide a where clause for it. Please put a where clause. — Qonvex620, Jan 18 '20 at 11:44
Drop index on field `expiration_date` and re-create it after update should improve update performance. — Slava Rozhnev, Jan 18 '20 at 12:14
Please post TEXT result of A) SHOW CREATE TABLE coupons; so we can see your existing table structure and indexes that are available. — Wilson Hauck, Jan 19 '20 at 18:30

score 2 · Answer 1 · answered Jan 18 '20 at 15:18

Others have pointed out that your UPDATE statement updates every row in the table. That, inherently, takes a long time. And, because of transaction / rollback in the database it will take a lot of RAM and disk space.

You didn't tell us how you defined your index or indexes on expiration_date, so this answer is a is a bit of guesswork. Specifically, unless expiration_date is in its own index or is the first column in a compound index this answer won't perform very well.

Try using an UPDATE query like this, to do the update 1000 rows at a time.

UPDATE coupons 
   SET expiration_date='2020-06-06'
 WHERE expiration_date <> '2020-06-06'
 LIMIT 1000

Repeat the query until it updates no more rows. Completing the update will still take a while, but it won't monopolize the table, nor will it generate vast transactions.

Caution, don't try to run more than one of these queries at a time.

Update query execute very slow on mysql when more than 1 million records

1 Answers1