The fastest way to compare dates: UNIX_TIMESTAMP or FROM_UNIXTIME or string

Question

What is the fastest way to select rows with a field exceeding a certain date?

WHERE date > FROM_UNIXTIME(12345)

or

WHERE UNIX_TIMESTAMP(date) > 12345

or

WHERE date > '2011-05-01 09:00:00'

Answer 1

The slowest:

WHERE UNIX_TIMESTAMP(date) > 12345

because the unix_timestamp function must be called every time for each row, and the index in the date column, if present, will not be used. The rest are almost equivalent:

WHERE date > FROM_UNIXTIME(12345)
WHERE date > '2011-05-01 09:00:00'

the from_unixtime function will be called only once, even the string will be parsed only once, it will not make any difference, but I prefer the latter because it is more readable.

Of course, be sure to include your date column:

alter table tablename add index idx_date (date)