List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 28 2006 8:56am
Subject:Re: HELP --- Slow SP
View as plain text  
> > 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
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