Mysql Table Update Query is very slow and its increasing day by day

Question

We have a table which just for the update query it takes almost 200-300 secs, the total data size is almost 3,357,196 rows. We have the keys attached to it and the select query is very fast. Only the update query is taking a lot of time.

Mysql Db Engine: InnoDB

CREATE TABLE `t_strava_push` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `strava_user` int(20) DEFAULT NULL,
  `strava_workout` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `type` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `aspect` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `event_time` int(20) DEFAULT NULL,
  `body` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `processed` int(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `strava_user` (`strava_user`),
  KEY `strava_workout` (`strava_workout`)
) ENGINE=InnoDB AUTO_INCREMENT=3377747 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

The query we are trying to update :

UPDATE t_strava_push SET processed = 3 WHERE strava_workout = 2677850786

The table is being constantly updated by a cron job which runs every 2 minutes. And a lot of data is coming to the table every minute.

Check the MySQL profiling here

Table Details

Check the process which are running in that table

Answer 1

strava_workout is a string. But you are comparing it as a number, so the index is not being used.

Keep the types consistent!

UPDATE t_strava_push
    SET processed = 3
    WHERE strava_workout = '2677850786';