The THEORY behind both statements is fine 1. Have a primary, single column integer index, auto incrementing (probably) for every record 2. Have a primary index that uniquely identifies your data.
The advantage of 2 is that it is (usually) obvious what the unique characteristics of your data are, and so a natural PK emerges. The advantages of 1 are based on two premises: a. Indices are all about efficiency, and it the efficiency of comparing 4-byte integers is greater than the efficiency of comparing 51 characters of a combined key. b. Relationships need to be simple to allow point a. to work - if the author wishes, some time in the future, to create a relationship to this table, s/he has two choices - create the child table with a foreign key containing all three elements of the original primary key or add a new auto-increment primary key to his_msisdn_imei_activ_hist at that stage. If, at that time, the table is involved in a 24x7x52 system with 100s millions of records, then adding a new column and index may not be practical. So, if your system is a small, stable one and will remain that way, index efficiency is less of an issue, and the use of a 51-byte multi column index is not a problem. However, if you want to design in future proofing, get in to the habit of putting a single column integer, auto_increment primary key on every table (or at least considering doing so!) The speed of MySQL can lead to some bad habits that don't transfer well to other DBMS products, and good practice is good practice anywhere. Have an awesome day. Quentin P.S. 51 bytes assumes DATETIME is 8 bytes, but it may be 6? -----Original Message----- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, 28 April 2006 8:56 p.m. To: mysql@lists.mysql.com Subject: Re: HELP --- Slow SP > > CREATE TABLE `his_msisdn_imei_activ_hist` ( > > `MSISDN` varchar(23) NOT NULL, > > `ACTIV_IMEI` varchar(20) NOT NULL, > > `ACTIV_PHONE_TYPE` varchar(100) NOT NULL, > > `PREV_IMEI` varchar(20) default NULL, > > `PREV_PHONE_TYPE` varchar(100) default NULL, > > `ACTIV_TIME` datetime NOT NULL, > > PRIMARY KEY (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`) > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > > > This primary key is a bad idea. A VERY VERY bad idea. For starters, a > primary key should have ONE field, not THREE. While it is allowed, it's > not going to help performance at all. Next is that the primary key Care for a fight over this one? :-) A primary key should be the primary key. If this is 3 columns, or 1 varchar column, it's all fine. I agree with your point of the ACTIV_TIME being a bad candidate for being part of a PK though. Oh, and having multiple columns in a PK does not mean you cannot create additional indices as/if required. All in all, your statement about multiple columns in a PK is a very very bad statement ;-) > should be a numeric field. You've got varchars and datetimes! Yuck! If > you want to enforce a rule such as restricting duplicate values, then > start by creating yourself a sane primary key ( an unsigned int, for > example ), and *THEN* put an index ( with your "don't allow duplicates" > rule ) across your (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`) fields. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
