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:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 17:09 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? Is it better to set multiple primary keys or to set one key and index the other columns? If I have a primary key as a field in another table should it also be set as a key? Adam On Wednesday, July 9, 2003, at 10:41 AM, <[EMAIL PROTECTED]> 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... > 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" <[EMAIL PROTECTED]> > An: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > 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 >> [EMAIL PROTECTED] >> www.cgps.org >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
