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.
