INT vs. TIMESTAMP data types


Currently for a blog platform I have:


I was contemplating using the TIMESTAMP type.

To get previous usage like yearly monthly etc archives I would do:

WHERE author=4 AND YEAR(timestamp)=2008 AND MONTH(timestamp)=4

Would this utilize an index on INDEX(author, timestamp) or would the functions YEAR() and MONTH() which extract kill the index usage?


It will not be able to use the index for the YEAR and MONTH parts.
But it will still use the index to narrow down the author

You can work around using YEAR() by converting the date boundaries to timestamps. For instance, that clause can use your whole index:

WHERE author=4 AND timestamp BETWEEN UNIX_TIMESTAMP(‘2008-04-01’) AND UNIX_TIMESTAMP(LAST_DAY(‘2008-04-01’))

Of course, you can also convert the dates using your favourite programming language if you prefer.

Thanks for clearling that up and the helpful tips chaps!