List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:April 28 2006 12:19am
Subject:Re: HELP --- Slow SP
View as plain text  
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
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