Certainly datetime is the way to go. It takes up 8 bytes per row, as opposed to 4 bytes for int. But, even if there are 10 million rows (over 27 years of data), that’s only a 4 MB difference.
> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 10, 2003 5:13 PM > To: [EMAIL PROTECTED] > Subject: Re: Can mysql handle this load? > > > why use INT for a date? > i am used to do this with my bulletin board, since i need a > 1-second resolution and so i can easily use the time() > function in php and format the output string with date(), > which is also using unix timestamps. > but for applications that only need a resolution of 1 day, > something like DATE would be better, i think. for client > side, it's more processing to get the date displayed and to > do some arithmetics with it (calculate time spans etc.), right? > > correct me if i'm wrong, since i had some chaotic encounters > with DATE and TIMESTAMP values at the beginning of my 'mysql > time', and i'm using INT unix timestamps since then... > > -yves > > > -----Ursprüngliche Nachricht----- > Von: "Rudy Metzger" <[EMAIL PROTECTED]> > An: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Adam > Gerson" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Gesendet: Mittwoch, 9. Juli 2003 17:19 > Betreff: RE: Can mysql handle this load? > > > Why using int for date? Better suited would be DATE or > DATETIME (or even TIMESTAMP, depending how you want to use it). > For studentid, SMALLINT or MEDIUMINT would maybe suffice, esp > when you make them UNSIGNED. > For status I would choose CHAR(1), you can put a lot of > information into that, which also stays (a bit) human > readable. Also enums would be ok but are a mess to change > later (in the application). Do yourself a favor and use a > master detail relation for this, eg: > > CREATE TABLE student_status ( > Status CHAR(1) NOT NULL, /* short status flag, eg. A */ > Verbose VARCHAR(20) NOT NULL, /* verbose description, > e.g. ABSENT */ > PRIMARY KEY(status) > ) > > Maybe keep 'verbose' on char to force fixed line size and > thus faster access. > > Cheers > /rudy > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: woensdag 9 juli 2003 16:42 > To: [EMAIL PROTECTED]; Adam Gerson > Cc: [EMAIL PROTECTED] > Subject: Re: Can mysql handle this load? > > i think this should be no problem... > > i'd think of some table layout like this: > date int PRIMARY > student_id int PRIMARY > status int > extra_data what-you-want > > then you should get about 360,000 records per year. > i saw people on this list reporting about millions of records > etc... and i guess they had a little greater tables than you > should get here. > > but why would you want to move any previous records to > another table all the time? just keep it in one table and > back up anything older than 5 years or so. that keeps your > table at, say 50 MB, and you can run real-time queries anytime :) > > -yves > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
