Re: DATETIME vs. INT(11)?

[Rick James]

MySQL does not seem to have any of the smarts you describe.

Your query, as it stands, will be a table scan (or possibly an index scan) regardless of what functions/datatypes/etc you use.

It could be an "index scan" instead of a "table scan" if all the fields mentioned in the select (purchase_date, in your case) are in an index. But this only deals with how much data it has to shovel through to get the answer. (An index is usually smaller than the table.)

DATETIME is stored as a string, like '20091231235959'. As it turns out,
LEFT(purchase_date, 6)
Gives you the desired grouping:

SELECT LEFT(purchase_date, 4) AS purchase_year,
       MID(purchase_date, 4, 2) AS purchase_month,
       COUNT(*) AS purchase_total
   FROM purchases
   GROUP BY LEFT(purchase_date, 6);

I don't know if it is smart enough to avoid the 'sort' that usually accompanies a GROUP BY, since it can gather the answer one row at a time.