List:General Discussion« Previous MessageNext Message »
From:Quentin Bennett Date:May 1 2006 8:43pm
Subject:RE: HELP --- Slow SP
View as plain text  
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:m.tonies@stripped]
Sent: Friday, 28 April 2006 8:56 p.m.
To: mysql@stripped
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/mysql?unsub=1
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.
Thread
HELP --- Slow SPHardi OK27 Apr
  • Re: HELP --- Slow SPDaniel Kasak28 Apr
  • Re: HELP --- Slow SPMartijn Tonies28 Apr
RE: HELP --- Slow SPQuentin Bennett28 Apr
  • Re: HELP --- Slow SPDaniel Kasak28 Apr
    • Re: HELP --- Slow SPHardi OK28 Apr
      • Re: HELP --- Slow SPDaniel Kasak28 Apr
RE: HELP --- Slow SPQuentin Bennett28 Apr
RE: HELP --- Slow SPQuentin Bennett1 May
  • Re: HELP --- Slow SPHardi OK3 May