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: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
