From: Rudy Metzger Date: July 14 2003 8:42am Subject: RE: Can mysql handle this load? List-Archive: http://lists.mysql.com/mysql/145084 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Just take care that you do not cast and or modify your datecolumn in the = where clause, no matter what data type you use. Because then you would = loose the index on it. So if you use int in you applications it might = indeed be wise to use int in the DB too. However when starting a new = application, I would use DATETIME on both sides (PPHP/perl,... and DB). Example: ... WHERE unix_timestamp(somedate) =3D 4858453 ... BAD, as MySQL cannot use the index anymore! ... WHERE date_add(somedate, 5 INTERVAL days) =3D '2003-05-23' ... BAD, again you loose the index ... WHERE somedate =3D date( "%Y-%m-D", mktime() ) ... (not sure about correctness of PHP syntax, but hope you get the meaning) GOOD, because you leave the MySQL date variable unchanged ... WHERE somedate =3D date( "%Y-%m-D", mktime()+5*60*60*24 ) ... GOOD, as you move the modification to the "constant" (not indexed) part = of the equation. Same counts for all other data type formats, of course. Cheers /rudy -----Original Message----- From: Adam Nelson [mailto:adam@stripped]=20 Sent: vrijdag 11 juli 2003 17:19 To: nospam@stripped; mysql@stripped Subject: RE: Can mysql handle this load? 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: nospam@stripped [mailto:nospam@stripped]=20 > Sent: Thursday, July 10, 2003 5:13 PM > To: mysql@stripped > Subject: Re: Can mysql handle this load? >=20 >=20 > why use INT for a date? > i am used to do this with my bulletin board, since i need a=20 > 1-second resolution and so i can easily use the time()=20 > function in php and format the output string with date(),=20 > which is also using unix timestamps. > but for applications that only need a resolution of 1 day,=20 > something like DATE would be better, i think. for client=20 > side, it's more processing to get the date displayed and to=20 > do some arithmetics with it (calculate time spans etc.), right? >=20 > correct me if i'm wrong, since i had some chaotic encounters=20 > with DATE and TIMESTAMP values at the beginning of my 'mysql=20 > time', and i'm using INT unix timestamps since then... >=20 > -yves >=20 >=20 > -----Urspr=FCngliche Nachricht-----=20 > Von: "Rudy Metzger" > An: ; ; "Adam=20 > Gerson" > Cc: > Gesendet: Mittwoch, 9. Juli 2003 17:19 > Betreff: RE: Can mysql handle this load? >=20 >=20 > Why using int for date? Better suited would be DATE or=20 > DATETIME (or even TIMESTAMP, depending how you want to use it). > For studentid, SMALLINT or MEDIUMINT would maybe suffice, esp=20 > when you make them UNSIGNED. > For status I would choose CHAR(1), you can put a lot of=20 > information into that, which also stays (a bit) human=20 > readable. Also enums would be ok but are a mess to change=20 > later (in the application). Do yourself a favor and use a=20 > master detail relation for this, eg: >=20 > CREATE TABLE student_status ( > Status CHAR(1) NOT NULL, /* short status flag, eg. A */ > Verbose VARCHAR(20) NOT NULL, /* verbose description,=20 > e.g. ABSENT */ > PRIMARY KEY(status) > ) >=20 > Maybe keep 'verbose' on char to force fixed line size and=20 > thus faster access. >=20 > Cheers > /rudy >=20 > -----Original Message----- > From: nospam@stripped [mailto:nospam@stripped]=20 > Sent: woensdag 9 juli 2003 16:42 > To: mysql@stripped; Adam Gerson > Cc: benchmarks@stripped > Subject: Re: Can mysql handle this load? >=20 > i think this should be no problem... >=20 > i'd think of some table layout like this: > date int PRIMARY > student_id int PRIMARY > status int > extra_data what-you-want >=20 > then you should get about 360,000 records per year. > i saw people on this list reporting about millions of records=20 > etc... and i guess they had a little greater tables than you=20 > should get here. >=20 > but why would you want to move any previous records to=20 > another table all the time? just keep it in one table and=20 > back up anything older than 5 years or so. that keeps your=20 > table at, say 50 MB, and you can run real-time queries anytime :) >=20 > -yves >=20 >=20 --=20 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: = http://lists.mysql.com/mysql?unsub=3Drudy.metzger@stripped