patternsqlMinor
Is a query with UNIX_TIMESTAMP using indexes in mysql?
Viewed 0 times
withqueryindexesmysqlusingunix_timestamp
Problem
If I use
in a SELECT, does this query still make use on an index, set on
.. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW()in a SELECT, does this query still make use on an index, set on
some_timestamp ?Solution
No. Up to now, the optimizer in all MySQL versions cannot use an index for such a condition. It is not sargable
If you want it to be able to use indexes, you'll have to use a function to rewrite/reverse your condition so no function is applied on the column:
What to use, depends on the column
If it is a
If
or the equivalent:
Check MySQL documentation:
If you want it to be able to use indexes, you'll have to use a function to rewrite/reverse your condition so no function is applied on the column:
WHERE some_timestamp > ReverseFunctionOf_UNIX_TIMESTAMP( NOW() )What to use, depends on the column
some_timestamp datatype. If it is a
TIMESTAMP, you can simply use WHERE some_timestamp> NOW()If
some_timestamp is an INT that stores Unix timestamps (seconds since '1970-01-01 00:00:00'), I think you had the correct function but in the wrong place. You should use:WHERE some_timestamp > UNIX_TIMESTAMP( NOW() )or the equivalent:
WHERE some_timestamp > UNIX_TIMESTAMP()Check MySQL documentation:
DATE and TIME functionsCode Snippets
WHERE some_timestamp > ReverseFunctionOf_UNIX_TIMESTAMP( NOW() )WHERE some_timestamp> NOW()WHERE some_timestamp > UNIX_TIMESTAMP( NOW() )WHERE some_timestamp > UNIX_TIMESTAMP()Context
StackExchange Database Administrators Q#28310, answer score: 4
Revisions (0)
No revisions yet.