0

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` < '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

  • I think that the index is not used cause you're selecting columns that are not included in the index, and a index seek + lookup is probably too expensive to do compared to a full table scan. Can you try to add to your index the columns status and can_be_closed? – Mattia Nocerino Jun 22 '18 at 9:44
1

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 < '2018-06-22 10:14:36'

and add

INDEX(status, can_be_closed, early_close)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.