simple update query taking very long to execute in MySQL (wait innodb)

I have a table with 54k rows, contains 10G of data

I am running this update query on it:

UPDATE my_table SET blog_object_version='19'  

takes more than 1 hour to run,
how can I improve performance?

additional information:

I am running on AMAZON rds, db.m5.4xlarge
this is my instance:

this is what I see in the aws performance insights:
wait/io/file/innodb/innodb_data_file

I do not have any other queries running on my db:

mysql> show processlist;  +----+----------+---------------------+----------+---------+------+----------+----------------------------------------------+  | Id | User     | Host                | db       | Command | Time | State    | Info                                         |  +----+----------+---------------------+----------+---------+------+----------+----------------------------------------------+  |  3 | rdsadmin | localhost:65182     | NULL     | Sleep   |    0 |          | NULL                                         |  |  4 | rdsadmin | localhost           | NULL     | Sleep   |    1 |          | NULL                                         |  |  6 | admin    | 123.45.67.890:6170  | my_table | Query   | 3901 | updating | UPDATE my_table SET blog_object_version='19' |  | 12 | admin    | 123.45.67.890:6360  | NULL     | Sleep   | 2981 |          | NULL                                         |  | 18 | admin    | 123.45.67.890:7001  | NULL     | Query   |    0 | starting | show processlist                             |  +----+----------+---------------------+----------+---------+------+----------+----------------------------------------------+  

and this is my table:

mysql> show create table my_table\G;  *************************** 1. row ***************************         Table: my_table  Create Table: CREATE TABLE `my_table` (    `index` int(11) NOT NULL AUTO_INCREMENT,    `id` varchar(100) DEFAULT NULL,    `user_id` varchar(50) NOT NULL,    `associate_object_id` varchar(50) NOT NULL,    `type` varchar(50) DEFAULT NULL,    `creation_date` datetime DEFAULT NULL,    `version_id` varchar(50) NOT NULL,    `blog_object` longtext,    `blog_object_version` varchar(100) DEFAULT NULL,    `last_update` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    PRIMARY KEY (`index`),    UNIQUE KEY `id_user_id_version_id` (`id`,`user_id`,`version_id`) USING BTREE,    KEY `user_id_associate_object_id` (`user_id`,`associate_object_id`),    KEY `user_id_associate_object_id_version_id` (`user_id`,`associate_object_id`,`version_id`)  ) ENGINE=InnoDB AUTO_INCREMENT=54563151 DEFAULT CHARSET=utf8  1 row in set (0.00 sec)  

these are my indexes:

mysql> SHOW INDEX FROM my_table;  +----------+------------+----------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  | Table    | Non_unique | Key_name                               | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |  +----------+------------+----------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  | my_table |          0 | PRIMARY                                |            1 | index               | A         |       43915 |     NULL | NULL   |      | BTREE      |         |               |  | my_table |          0 | id_user_id_version_id                  |            1 | id                  | A         |        3659 |     NULL | NULL   | YES  | BTREE      |         |               |  | my_table |          0 | id_user_id_version_id                  |            2 | user_id             | A         |        8783 |     NULL | NULL   |      | BTREE      |         |               |  | my_table |          0 | id_user_id_version_id                  |            3 | version_id          | A         |       43915 |     NULL | NULL   |      | BTREE      |         |               |  | my_table |          1 | user_id_associate_object_id            |            1 | user_id             | A         |         378 |     NULL | NULL   |      | BTREE      |         |               |  | my_table |          1 | user_id_associate_object_id            |            2 | associate_object_id | A         |        4391 |     NULL | NULL   |      | BTREE      |         |               |  | my_table |          1 | user_id_associate_object_id_version_id |            1 | user_id             | A         |         385 |     NULL | NULL   |      | BTREE      |         |               |  | my_table |          1 | user_id_associate_object_id_version_id |            2 | associate_object_id | A         |        6273 |     NULL | NULL   |      | BTREE      |         |               |  | my_table |          1 | user_id_associate_object_id_version_id |            3 | version_id          | A         |       43915 |     NULL | NULL   |      | BTREE      |         |               |  +----------+------------+----------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  

https://stackoverflow.com/questions/67316703/simple-update-query-taking-very-long-to-execute-in-mysql-wait-innodb April 29, 2021 at 07:43PM