List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:January 28 2009 6:43am
Subject:Re: Are dates stored as String? Or Integer?
View as plain text  
For the DATE type, the comparisons are performed aginst raw binary
data as an integer would be.  I read your first post too hastily and
thought you were proposigng to store  dates as string. (don't laugh, I
have seen this done by people who should know better).  I don't think
you are going to squeeze out any performance benefit by converting to
int..  you might even lose some ground to the special handling your
date-as-int your app might have to do.

 - michael dykman


On Wed, Jan 28, 2009 at 1:30 AM, mos <mos99@stripped> wrote:
> 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
>> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>



-- 
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.
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