Timestamp as int field, query performance

Question

Hello,

I'm storing timestamp as int field. And on large table it takes too long to get rows inserted at date because I'm using mysql function FROM_UNIXTIME.

SELECT * FROM table WHERE FROM_UNIXTIME(timestamp_field, '%Y-%m-%d') = '2010-04-04'

Is there any ways to speed this query? Maybe I should use query for rows using timestamp_field >= x AND timestamp_field < y?

Thank you

---

EDITED This query works great, but you should take care of index on timestamp_field.

SELECT * FROM table WHERE 
timestamp_field >= UNIX_TIMESTAMP('2010-04-14 00:00:00')
AND timestamp_field <= UNIX_TIMESTAMP('2010-04-14 23:59:59')

Answer 1

If you're able to, it would be faster to either store the date as a proper datetime field, or, in the code running the query, to convert the date you're after to a unix timestamp before sending it to the query.

The FROM_UNIXTIME would have to convert every record in the table before it can check it which, as you can see, has performance issues. Using a native datatype that is closest to what you're actually using in your queries, or querying with the column's data type, is the fastest way.

So, if you need to continue using an int field for your time, then yes, using < and > on a strict integer would boost performance greatly, assuming you store things to the second, rather than the timestamp that would be for midinight of that day.

Answer 2

Use UNIX_TIMESTAMP on the constant instead of FROM_UNIXTIME on the column:

SELECT * FROM table
WHERE timestamp_field
   BETWEEN UNIX_TIMESTAMP('2010-04-14 00:00:00')
       AND UNIX_TIMESTAMP('2010-04-14 23:59:59')

This can be faster because it allows the database to use an index on the column timestamp_field, if one exists. It is not possible for the database to use the index when you use a non-sargable function like FROM_UNIXTIME on the column.

If you don't have an index on timestamp_field then add one.

Once you have done this you can also try to further improve performance by selecting the columns you need instead of using SELECT *.