CODEWITHSUNDEEP
mysqlperformance
Tim Whitlock
Tim Whitlock

Reputation: 1111

Performance with timestamp conditions

Which of the following is faster, or are they equivalent? (grabbing recent most records from a TIMESTAMP COLUMN)

SELECT UNIX_TIMESTAMP(`modified`) stamp
FROM `some_table`
HAVING stamp > 127068799
ORDER BY stamp DESC

or 

SELECT UNIX_TIMESTAMP(`modified`) stamp
FROM `some_table`
WHERE UNIX_TIMESTAMP(`modified`) > 127068799
ORDER BY `modified` DESC

or even another combination?

Upvotes: 4

Views: 353

Answers (2)

Quassnoi
Quassnoi

Reputation: 425341

As long as modified is indexed, this one:

SELECT  UNIX_TIMESTAMP(`modified`) stamp
FROM    `some_table`
WHERE   modified > FROM_UNIXTIME(127068799)
ORDER BY
        modified DESC

is the best solution, since it is sargable and allows the index on modified to be used, unlike both of your queries.

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157860

Both equal and not so good ones, as every single row value have to be converted to timestamp

why not to leave date field as is, and convert only constant value? 

WHERE `modified` > FROM_UNIXTIME(127068799)

Upvotes: 1

Related Questions