List:General Discussion« Previous MessageNext Message »
From:mos Date:January 28 2009 6:30am
Subject:Re: Are dates stored as String? Or Integer?
View as plain text  
At 11:43 PM 1/27/2009, Michael Dykman wrote:
>Absolutely, there is a significant speed benefit from using
>appropriate date/timestamp will speed up your lookups.  They are
>stored not as ints per-se but are binary encoded in a similar manner.
>Read this carefully before before you undertake any conversions.
>
>http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html
>
>You may also save considerable space..
>
>from http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
>
>DATE    3 bytes
>TIME    3 bytes
>DATETIME        8 bytes
>TIMESTAMP       4 bytes
>YEAR    1 byte

Michael,
              So if I understand it correctly, if I switch from Date to 
Int(8) 4 bytes to represent 20080125, then the queries should be faster? 
Because it doesn't have to encode the date to a 3 byte integer or decode it 
when retrieving the date value?

Mike


>  - michael dykman
>
>On Wed, Jan 28, 2009 at 12:31 AM, mos <mos99@stripped> wrote:
> > Are date columns stored as String or Integer in a MyISAM table?
> >
> > I am trying to squeeze more speed from my application and a date column is
> > used in most of the indexes for my tables. I'm wondering if changing the
> > Date data type to an Integer is going to speed the queries up. I'm using
> > Delphi and internally it represents dates as float so using integers will
> > speed up the Delphi code. But the main slow down I have is with executing
> > the queries. The dates are used in the indexes, sorting, and in a few table
> > joins. So is there a speed advantage of switching the dates to integer?
> >
> > TIA
> >
> > Mike
> >

Thread
Are dates stored as String? Or Integer?mos28 Jan
  • Re: Are dates stored as String? Or Integer?Michael Dykman28 Jan
    • Re: Are dates stored as String? Or Integer?mos28 Jan
      • Re: Are dates stored as String? Or Integer?Michael Dykman28 Jan
  • Re: Are dates stored as String? Or Integer?Paul DuBois28 Jan
  • Re: Are dates stored as String? Or Integer?Martijn Tonies29 Jan