From: Mike Hillyer Date: July 9 2003 3:18pm Subject: RE: Can mysql handle this load? List-Archive: http://lists.mysql.com/mysql/144766 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Well that all depends. The real reason for a primary key is to prevent = duplicates, therefore, if a combination of fields needs to be unique, = then a multiple primary key makes sense, especially if other tables will = reference the field combination (for example, detail items on an invoice = where the invoice number will not be unique, and the detail number will = not be unique, but the combination of the two will be). As for question #2, if two fields, one on each table, will be used to = join tables together, both fields should be indexed. Regards, Mike Hillyer www.vbmysql.com > -----Original Message----- > From: Adam Gerson [mailto:agersonl@stripped]=20 > Sent: Wednesday, July 09, 2003 9:09 AM > To: nospam@stripped > Cc: mysql@stripped; benchmarks@stripped > Subject: Re: Can mysql handle this load? >=20 >=20 > Is it better to set multiple primary keys or to set one key and index=20 > the other columns? If I have a primary key as a field in=20 > another table=20 > should it also be set as a key? >=20 >=20 >=20 > Adam >=20 > On Wednesday, July 9, 2003, at 10:41 AM,=20 > wrote: >=20 > > 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=20 > records etc...=20 > > and i guess they had a little greater tables than you=20 > should get here. > > > > but why would you want to move any previous records to=20 > another table=20 > > all the time? just keep it in one table and back up anything older=20 > > than 5 years or so. that keeps your table at, say 50 MB,=20 > and you can=20 > > run real-time queries anytime :) > > > > -yves > > > > > > -----Urspr=FCngliche Nachricht----- > > 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 > >> little less then 1000 students. For every day of the=20 > school year one > >> record will be entered into a table for each student representing=20 > >> their > >> attendance status (present, absent, late, etc...). I also=20 > have several > >> other supporting tables for relationships. When it comes=20 > to reporting > >> and querying this DB I am worried that it will very quickly become=20 > >> 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=20 > things over > >> in the middle of the night. This way any operations on the current=20 > >> 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 > >> > >> > >> --=20 > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: =20 > >> http://lists.mysql.com/mysql?unsub=3Dnospam@stripped > >> > > >=20 >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: =20 > http://lists.mysql.com/mysql?> unsub=3Dmhillyer@stripped >=20 >=20