Re: DATETIME vs. INT(11)?

[yawnmoth]

Here's the "real" query:

SELECT from_unixtime(purchase_date,'%Y') AS purchase_year,
from_unixtime(purchase_date,'%m') AS purchase_month, COUNT(*) AS
purchase_total
FROM purchases
GROUP BY purchase_year, purchase_month;

Problem is that the query is slow - presumably because from_unixtime() has to be run on every column. On the surface, it seems like month() and year() would be slow for the same reason, but I initially thought that datetime might internally represent dates as three columns and that month(column_name) would just tell MySQL to look at that "hidden" column, specifically. If that's not what it's doing than I'm not really sure I see the point.

Sure, there are lots of functions that you can use with DATETIME and whatnot but you can emulate those functions pretty easily, as is. For example, AVG(DATEDIFF(end, start)) == AVG(end - start) / (60*60*24).