Search code examples
sqlmysqlmariadbquery-optimization

Speed up UPDATE query (one table with index)

there. Is it possible to speed up my UPDATE query?

DB mariadb:10.3

-- dev_db_5843.subscription

CREATE TABLE `subscription`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `work_to_dt` datetime DEFAULT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_subscription_work_to_dt` (`work_to_dt`) USING BTREE,
)
ENGINE=InnoDB AUTO_INCREMENT=4187656 DEFAULT CHARSET=utf8;

and i have sql query

UPDATE subscription
SET `status`= 2
WHERE
    subscription.`status` = 1 AND
    subscription.work_to_dt IS NOT NULL AND
    subscription.work_to_dt < '2024-04-01 14:30:31';

Table subscription has 3 629 036 rows. Rows for UPDATE query is 958.

EXPLAIN
UPDATE subscription
SET `status`= 2
WHERE
    subscription.`status` = 1 AND
    subscription.work_to_dt IS NOT NULL AND
    subscription.work_to_dt < '2024-04-01 14:30:31';

enter image description here

UPDATE duration is about 5 seconds enter image description here

If remove index from work_to_dt column I have the same query duration about ~5 seconds enter image description here

Why it happens? I thought that column index must speed up my update query, that's wrong i do?

Solution

  • subscription.work_to_dt IS NOT NULL is not necessary as NULL isn't <= to anything.

    The best index for your case will be (status, work_to_date) as a single composite index (not two separate indexes). This will find the rows to update quickest.

    Having just (work_to_date) will mean that all the rows with status = 1 will be mixed in with all the other status values too. Those other rows will still need to be scanned and then actively ignored.