I have this index:
CREATE INDEX `global_myDateIdx`
ON `models`(`realDate`)
USING GSI WITH {"defer_build":true}
And I’m running some queries that can look like this:
select * from models
where STR_TO_MILLIS(realDate) <= STR_TO_MILLIS(userInputtedDate)
Which produces an EXPLAIN execution plan, which does not use the index but does a primary scan. (Couchbase 4.6.0)
The date format stored within the field looks like this:
2016-12-31T00:00:00.000Z
Always guaranteed to be in that format, always guaranteed to be UTC.
My question is, how should date range queries be done so as to exploit the indexes? Should I be arranging data so that the string comparison and the date comparison are the same? I have noticed that if I don’t use the STR_TO_MILLIS
function that the index will be used in the query plan