List:General Discussion« Previous MessageNext Message »
From:Rudy Metzger Date:July 9 2003 3:19pm
Subject:RE: Can mysql handle this load?
View as plain text  
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

 
-----Ursprüngliche Nachricht----- 
Von: "Adam Gerson" <agersonl@stripped>
An: <mysql@stripped>
Cc: <benchmarks@stripped>
Gesendet: Mittwoch, 9. Juli 2003 15:46
Betreff: Can mysql handle this load?


> I am writing an attendance system in php for my school. We have a 
> little less then 1000 students. For every day of the school year one 
> record will be entered into a table for each student representing their 
> attendance status (present, absent, late, etc...). I also have several 
> other supporting tables for relationships. When it comes to reporting 
> and querying this DB I am worried that it will very quickly become very 
> large and slow. Can mysql handle this? Are there any techniques to 
> speed it up? I will trying indexing major columns.
> 
> I have also considered keeping all previous days attendance in a 
> separate table from the current days attendance and moving things over 
> in the middle of the night. This way any operations on the current days 
> data will go quickly, but reports on long term things will still be 
> slow. Good idea?
> 
> Thanks,
> Adam
> 
> 
> 
> 
> 
> -------------------------------
> Adam Gerson
> Systems Administrator / Computer Teacher
> Columbia Grammar and Prep School
> 212-749-6200
> agerson@stripped
> www.cgps.org
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Access deniedAsterix11 Mar
  • Re: Access deniedChristian Mack11 Mar
  • Re: Can mysql handle this load?nospam9 Jul
  • RE: Can mysql handle this load?Andy Eastham9 Jul
  • Re: Can mysql handle this load?Krasimir_Slaveykov10 Jul
  • Left Join - Revistedvernon10 Jul
RE: Can mysql handle this load?Mike Hillyer9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: order of table joins or where clauses relevant?Rudy Metzger10 Jul
RE: Left Join - RevistedRudy Metzger11 Jul
RE: Can mysql handle this load?Rudy Metzger14 Jul