List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:April 27 2006 11:37pm
Subject:Re: HELP --- Slow SP
View as plain text  
Hardi OK wrote:
> Hi Forums,
>
> I have a frustrating problem on my Stored Procedure. It can only proccess
> about 100 records in 10 minutes. I have 2 million initial records that need
> to processed. Meaning that with this speed i will around 200 days to finish
> all of them.
> To make it worse, the data itself grows at least another 100 records per
> hour.
>
> Really appreciated if anybody can help to speed this up.
>   

Without looking too much at the actual SP, I can tell you now that 
you're not using any indexes AT ALL.

> 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 
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.

Next point is that MySQL will only make use of an index in a join or a 
where clause if ONLY that field is included in the index. If you pack 3 
fields into an index and then try to join on ONLY ONE field, the index 
can't be used. So look at your joins and where clauses and make sure 
your indexes match.


-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au
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