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

Answer 1

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.