How to optimize update in mysql?

Question

MySQL

There is a table:

CREATE TABLE test.sites_list (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  url varchar(255) NOT NULL COMMENT 'Домен',
  reg varchar(100) DEFAULT NULL COMMENT 'Регистратор',
  domain_created date DEFAULT NULL COMMENT 'Дата регистрации домена',
  last_http_code int(11) NOT NULL COMMENT 'Последний статус ответа сервера',
  created_at timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'Время создания',
  updated_at timestamp DEFAULT '0000-00-00 00:00:00',
  status int(11) NOT NULL COMMENT 'Статус',
  marker varchar(255) NOT NULL DEFAULT '',
  proc varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX domainCreated_updatedAt (domain_created, updated_at),
  INDEX Status_Proc_dCreated (status, proc, domain_created, updated_at),
  INDEX UpdatedAt_Status (updated_at, status)
)
ENGINE = INNODB
AUTO_INCREMENT = 19795014
AVG_ROW_LENGTH = 447
CHARACTER SET utf8
COLLATE utf8_general_ci;

There is a request:

UPDATE `sites_list` SET `proc` = '2cc16b53f60654d97bcfb265eb0082a4'  WHERE `status` = 0 AND `proc` = '' ORDER BY `domain_created` DESC, `updated_at` LIMIT 50;

Situation:
There are more than 3 million records in the table.
This request takes several minutes to complete.
The SHOW PROFILE request shows that all the time is spent on init.
How can UPDATE be optimized or compromised?

Answer 1

Is there a composite index on proc and status?

Answer 2

INDEX Status_Proc_dCreated (status, proc, domain_created, updated_at),

Answer 3

Here are all indexes:

INDEX domainCreated_updatedAt (domain_created, updated_at),
INDEX Status_Proc_dCreated (status, proc, domain_created, updated_at),
INDEX UpdatedAt_Status (updated_at, status)

status, proc can be taken from the second, and domain_created, updated_at - from the first, although EXPLAIN SELECT only shows Status_Proc_dCreated

Answer 4

The cards say that it could be a different sort order in the ORDER BY. Try ORDER BY `domain_created` DESC, `updated_at` DESCif the application logic allows.
In general, it would be nice to see

EXPLAIN SELECT * FROM `sites_list`
WHERE `status` = 0 AND `proc` = '' 
ORDER BY `domain_created` DESC, `updated_at` LIMIT 50

Answer 5

EXPLAIN SELECT * FROM `sites_list`
WHERE `status` = 0 AND `proc` = '' 
ORDER BY `domain_created` DESC, `updated_at` LIMIT 50

Answer 6

Try also looking at the request profile with SHOW PROFILE .