From: Rudy Metzger Date: July 9 2003 3:33pm Subject: RE: Can mysql handle this load? List-Archive: http://lists.mysql.com/mysql/144771 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MySQL allows only ONE primary key per table, so you can only use one. = However, you can also define UNIQUE INDEXES. The major difference here = is that primary key columns may not contain NULL values, however UNIQUE = KEY columns may contain NULL values. If your question now is: Is it better to use a multi part/column key or = split it up in multiple keys, I would suggest that you always should = make the primary key on the lowest number of columns you can get. So no = need to define a primary key on (studentid,gender) if (studentid) by = itself is already unique/primary. Please also note that on multipart = keys the key is only used if you provide at least the leading columns. = So on a PK(a,b,c) the key is not taken when you search for b or c or b = and c (but is taken if you search for a / a,b / a,b,c and even a,c (but = then only a is taken). So to answer your question: it depends on your situation. In general = (for 90% of the cases), make a primary key as short as possible and add = indexes as you need them (use EXPLAIN to see how the optimizer is = parsing the kwiri). But keep in mind that although indexes speed up = kwiries (SELECTS) they slow down UPDATES/INSERTS/DELETES. Cheers /rudy ps: as always, exceptions confirm the rule :) -----Original Message----- From: Adam Gerson [mailto:agersonl@stripped]=20 Sent: woensdag 9 juli 2003 17:09 To: nospam@stripped Cc: mysql@stripped; benchmarks@stripped Subject: Re: Can mysql handle this load? 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 another table=20 should it also be set as a key? Adam On Wednesday, July 9, 2003, at 10:41 AM, wrote: > 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...=20 > 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=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, 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 school year one >> record will be entered into a table for each student representing=20 >> 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=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 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 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: = http://lists.mysql.com/mysql?unsub=3Drudy.metzger@stripped