select *
from table
where '2022-09-30 13:05:30.000' between concat(start_date, ' 00:00:00.000')
and concat(end_date, ' 23:59:59.999')
The above example does not use index because it uses the concat function What should I do?
start_date, end_date type ‘yyyy-MM-dd’
2
Answers
Convert to
DATETIME
usingSTR_TO_DATE
The query needs to avoid manipulating columns inside the
WHERE
. That makes the condition not sargablePlease clarify what datatypes are being used. It seems that
start_date
andend_date
areDATE
, notDATATIME
. Yet the literal ‘2022-09-30 13:05:30.000’ seems to be aDATATIME
(more specificallyDATETIME(3)
)A
DATE
and aDATETIME
can be compared, so this is fine without tacking on the 0 time:except that that will not catch anything from the 30th.
The other end is messier, since a
DATE
maps to midnight of the morning of that date. Either of these works:Except that your "end_date" is probably set to ‘2022-09-30’, implying "any time that day"?
also works. Now it is "sargable" because the Optimizer can evaluate
DATE(constant)
before trying to perform the query.But… What is the index? There _is no index for "betweenness" on dates or datetimes". The Optimizer does not have the concept, for example, that an "end date" is always later than a "start date". And many other assumptions.
Even if you have an index involving start_date and/or end_date, only one of them will be used. This is because the Optimizer has no way to do two "range" tests at the same time. If it uses one of them, then it is likely to be scanning the first half or the last half of the table. At this point, it won’t bother wasting time with the index.
If you would care to describe the application further, I may have some other tricks up my sleeve. Here’s one that is very efficient (but complex) for a similar task: http://mysql.rjweb.org/doc.php/ipranges