how to index concat date time from mysql-CodePudding

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'

CodePudding user response：

Convert to DATETIME using STR_TO_DATE

SELECT
    *
FROM
    `table`
WHERE
    STR_TO_DATE(
        '2022-09-30 13:05:30.000', '%Y-%m-%d %H:%i:%s.%f'
    ) BETWEEN STR_TO_DATE(
        CONCAT(start_date, ' 00:00:00.000'), '%Y-%m-%d %H:%i:%s.%f'
    )
    AND STR_TO_DATE(
        CONCAT(end_date, ' 23:59:59.999'), '%Y-%m-%d %H:%i:%s.%f'
    )

CodePudding user response：

The query needs to avoid manipulating columns inside the WHERE. That makes the condition not sargable

Please clarify what datatypes are being used. It seems that start_date and end_date are DATE, not DATATIME. Yet the literal '2022-09-30 13:05:30.000' seems to be a DATATIME (more specifically DATETIME(3))

A DATE and a DATETIME can be compared, so this is fine without tacking on the 0 time:

start_date >= '2022-09-30 13:05:30.000'

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:

end_date > '2022-09-30'
end_date > '2022-09-30 13:05:30.000'

Except that your "end_date" is probably set to '2022-09-30', implying "any time that day"?

end_date >= DATE('2022-09-30 13:05:30.000')

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