From: Rudy Metzger Date: July 9 2003 3:19pm Subject: RE: Can mysql handle this load? List-Archive: http://lists.mysql.com/mysql/144768 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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]=20 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 =20 -----Urspr=FCngliche Nachricht-----=20 Von: "Adam Gerson" An: Cc: 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=20 > little less then 1000 students. For every day of the school year one=20 > record will be entered into a table for each student representing = their=20 > attendance status (present, absent, late, etc...). I also have several = > other supporting tables for relationships. When it comes to reporting=20 > and querying this DB I am worried that it will very quickly become = very=20 > large and slow. Can mysql handle this? Are there any techniques to=20 > speed it up? I will trying indexing major columns. >=20 > I have also considered keeping all previous days attendance in a=20 > 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=20 > data will go quickly, but reports on long term things will still be=20 > slow. Good idea? >=20 > Thanks, > Adam >=20 >=20 >=20 >=20 >=20 > ------------------------------- > Adam Gerson > Systems Administrator / Computer Teacher > Columbia Grammar and Prep School > 212-749-6200 > agerson@stripped > www.cgps.org >=20 >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: = http://lists.mysql.com/mysql?unsub=3Dnospam@stripped >=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