INT vs. TIMESTAMP data types

MySQL & MariaDB Other MySQL® Questions
1

Hi,

Currently for a blog platform I have:

year=YEAR
month=TINYINT
day=TINYINT
timestamp=INT

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?

Cheers!

2

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

3

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.

4

Thanks for clearling that up and the helpful tips chaps!