Date VS Integer SELECT

[perrohunter]

Hi !

What do you think guys ?

I making a database, and I know it is going to be huge, and I was wondering to use

a int(8) field or a date() field

this because I think MySQL gets results faster on integers, however, date must have some sort of optimization, I dont know

what do u guys think ? use 20080302 or 2008-03-02 as date format ?

[NogDog]

I normally prefer to use the type that most closely approximates the type of data; so by that criteria I would use a DATE or DATETIME (or TIMESTAMP) type as applicable. One important benefit in this case is that you can then use the rich supply of built-in MySQL date/time functions in conjunction with that field, avoiding the need of type conversions.

As far as performance, I'm not aware of any particular advantages of either type in typical usage (but there are many things I'm not aware of!). A DATE type requires just 3 bytes for storage, a TIMESTAMP 4 bytes, and a DATETIME 8 bytes, while a regular INTEGER requires 4 bytes (which would be the type needed to store a UNIX timestamp integer value).