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 <mo...@fastmail.fm> 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 <mo...@fastmail.fm> 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=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
