Re: HELP --- Slow SP
Guys, So many thanks for you kind help. I was able to find the culprit, just adding 1 index i can get as fast as 30 thousands record per hour. So that I can proccess 2 million data in about 4 days only. But now, after my main tables loaded with more than 2 million data (2,9 million), i have another problem with the same SP. After some time (about each 25 execution loops), i always got this error message: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction How can this be happening since I am the only who is doing anything with the DB? Rgds/Hardi
RE: HELP --- Slow SP
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]
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]
Re: HELP --- Slow SP
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]
RE: HELP --- Slow SP
[snip] 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. [\snip] I think that you can use the left most columns of the index, without including the remainder. So a join on MSISDN or MSIISDN and ACTIV_IMEI may use the index (depending on whatever else is going on) but a join on ACTIV_IMEI only or MSISDN and ACTIV_TIME won't. Quentin -- 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] 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]
Re: HELP --- Slow SP
Quentin Bennett wrote: I think that you can use the left most columns of the index, without including the remainder. That's wasn't my understanding of how things work, but I've just checked the documentation, and it looks like you're right: docs MySQL cannot use a partial index if the columns do not form a leftmost prefix of the index. Suppose that you have the |SELECT| statements shown here: SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/; SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/ AND col2=/|val2|/; SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/; SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/ AND col3=/|val3|/; If an index exists on |(col1, col2, col3)|, only the first two queries use the index. The third and fourth queries do involve indexed columns, but |(col2)| and |(col2, col3)| are not leftmost prefixes of |(col1, col2, col3)|. /docs That's pretty strange. Anyway, what output do you get if you put 'explain ' in front of your queries? Are the indexes being used? -- 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]
Re: HELP --- Slow SP
Hi, When I used the EXPLAIN command, i see that all of my query are using the correct index. That's why i was quite sure that index won't be the cause of my slow query problem. So, i now should alter the table: remove primary key and recreate index? Many thanks, Hardi On 4/28/06, Daniel Kasak [EMAIL PROTECTED] wrote: Quentin Bennett wrote: I think that you can use the left most columns of the index, without including the remainder. That's wasn't my understanding of how things work, but I've just checked the documentation, and it looks like you're right: docs MySQL cannot use a partial index if the columns do not form a leftmost prefix of the index. Suppose that you have the |SELECT| statements shown here: SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/; SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/ AND col2=/|val2|/; SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/; SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/ AND col3=/|val3|/; If an index exists on |(col1, col2, col3)|, only the first two queries use the index. The third and fourth queries do involve indexed columns, but |(col2)| and |(col2, col3)| are not leftmost prefixes of |(col1, col2, col3)|. /docs That's pretty strange. Anyway, what output do you get if you put 'explain ' in front of your queries? Are the indexes being used? -- 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
Re: HELP --- Slow SP
Hardi OK wrote: Hi, When I used the EXPLAIN command, i see that all of my query are using the correct index. That's why i was quite sure that index won't be the cause of my slow query problem. So, i now should alter the table: remove primary key and recreate index? If your queries are using the index, then setting up a new primary key and indexes will not help. Personally, I would do it anyway, but this won't help out in your current case. -- 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]
RE: HELP --- Slow SP
Hi Hardi, You may need to supply a bit more information - table formats, query string, output from EXPLAIN - to allow more detailed diagnosis. Quentin -Original Message- From: Hardi OK [mailto:[EMAIL PROTECTED] Sent: Friday, 28 April 2006 1:14 p.m. To: Daniel Kasak Cc: Quentin Bennett; mysql@lists.mysql.com Subject: Re: HELP --- Slow SP Hi, When I used the EXPLAIN command, i see that all of my query are using the correct index. That's why i was quite sure that index won't be the cause of my slow query problem. So, i now should alter the table: remove primary key and recreate index? Many thanks, Hardi On 4/28/06, Daniel Kasak [EMAIL PROTECTED] wrote: Quentin Bennett wrote: I think that you can use the left most columns of the index, without including the remainder. That's wasn't my understanding of how things work, but I've just checked the documentation, and it looks like you're right: docs MySQL cannot use a partial index if the columns do not form a leftmost prefix of the index. Suppose that you have the |SELECT| statements shown here: SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/; SELECT * FROM /|tbl_name|/ WHERE col1=/|val1|/ AND col2=/|val2|/; SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/; SELECT * FROM /|tbl_name|/ WHERE col2=/|val2|/ AND col3=/|val3|/; If an index exists on |(col1, col2, col3)|, only the first two queries use the index. The third and fourth queries do involve indexed columns, but |(col2)| and |(col2, col3)| are not leftmost prefixes of |(col1, col2, col3)|. /docs That's pretty strange. Anyway, what output do you get if you put 'explain ' in front of your queries? Are the indexes being used? -- 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 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.