MySQL Forums
Forum List  »  Newbie

Re: DATETIME vs. INT(11)?
Posted by: Rick James
Date: August 26, 2009 11:30AM

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.

Options: ReplyQuote

Written By
August 14, 2009 10:00AM
August 15, 2009 03:26PM
August 17, 2009 10:31AM
August 17, 2009 08:04PM
August 26, 2009 09:23AM
Re: DATETIME vs. INT(11)?
August 26, 2009 11:30AM
August 26, 2009 11:55AM
August 26, 2009 11:34PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.