Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

If I use 

.. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW()

in a SELECT, does this query still make use on an index, set on some_timestamp ?

share|improve this question

1 Answer

up vote 1 down vote accepted

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:

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 functions

share|improve this answer
FROM_UNIXTIME() does the reverse of UNIX_TIMESTAMP() – rubo77 Nov 7 '12 at 8:15
Yes. But what is best to use, depends on the column datatype. If it is a TIMESTAMP, you can simply use column > NOW() – ypercube Nov 7 '12 at 8:16
If it is an INT that stores Unix timestamps (seconds since '1970-01-01 00:00:00'), you should use column > UNIX_TIMESTAMP( NOW() ) – ypercube Nov 7 '12 at 8:23
thanks, the sargable issue was new to me. i think i will have a lot of fun with that new knowledge ;) – rubo77 Nov 7 '12 at 11:08
@rubo77: Bear in mind that the Wikipedia article is mostly correct but not 100% accurate. The examples of non-surgable conditions are very good (and the rewrites). The parts though that say "left part of a sql condition", you can safely ignore. – ypercube Nov 7 '12 at 12:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.