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]
> Sent: Thursday, July 10, 2003 5:13 PM
> To: mysql@stripped
> 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" <rudy.metzger@stripped>
> An: <nospam@stripped>; <mysql@stripped>; "Adam
> Gerson" <agersonl@stripped>
> Cc: <benchmarks@stripped>
> 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: nospam@stripped [mailto:nospam@stripped]
> Sent: woensdag 9 juli 2003 16:42
> To: mysql@stripped; Adam Gerson
> Cc: benchmarks@stripped
> 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
>
>
