2 more complex
source | link

OptimalPlan 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)

Optimal is to list = column(s) first, then a range:

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

Cookbook

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)
1
source | link

Optimal is to list = column(s) first, then a range:

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

Cookbook