Indexes for select performance I have a table:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` enum('new','paid') NOT NULL DEFAULT 'new',
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `can_be_closed` tinyint(1) NOT NULL DEFAULT '1',
  `close_reason` varchar(255) DEFAULT NULL,
  `interval_before_close` int(11) NOT NULL DEFAULT '10',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


And following select: 

SELECT `id`, `status`, `close_reason` AS `closeReason`, `createdAt`, `updatedAt` 
FROM `t1` as t
WHERE (
`t`.`status` = 'new' 
AND `t`.`updatedAt` &lt; '2018-06-22 10:14:36' + INTERVAL `interval_before_close` MINUTE 
AND `t`.`can_be_closed` = true
)


For this query, explain return a full table scan.

I tried to add the following indexes:

ALTER TABLE `t1` 
ADD INDEX `updatedAt` (`updatedAt` ASC);


and:

ALTER TABLE `t1` 
ADD INDEX `updatedAt` (`updatedAt` ASC, `interval_before_close` ASC);


but without any change in explain.

Fidle ex.: link
Plan A (not good):

Off hand, I would say it is optimal is to list = column(s) first, then a range:

INDEX(status, can_be_closed,   -- in either order
      updatedAt)               -- last


But...  It is more complicated than that.

'2018...' + INTERVAL interval_before_close ...


is essentially a function call to compute a date.  That makes it useless to put either updatedAt or interval_before_close in the index.

Plan B (wimpy):

So...  The following may be useful, but only if not many of the rows match those two flags:

INDEX(status, can_be_closed)   -- in either order


Cookbook

Plan C (complex):

If, on the other hand, you had a column (real or computed) that contained

updatedAt - INTERVAL interval_before_close MINUTE


Say that it column is called early_close; then change to

AND early_close &lt; '2018-06-22 10:14:36'


and add

INDEX(status, can_be_closed, early_close)