List:General Discussion« Previous MessageNext Message »
From:Quentin Bennett Date:April 28 2006 2:12am
Subject:RE: HELP --- Slow SP
View as plain text  
Hi Hardi,
You may need to supply a bit more information - table formats, query string, output from
EXPLAIN - to allow more detailed diagnosis.

-----Original Message-----
From: Hardi OK [mailto:hahahardididi@stripped]
Sent: Friday, 28 April 2006 1:14 p.m.
To: Daniel Kasak
Cc: Quentin Bennett; mysql@stripped
Subject: Re: HELP --- Slow SP

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,

On 4/28/06, Daniel Kasak < dkasak@stripped> 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:


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

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


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: dkasak@stripped

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.

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