List:General Discussion« Previous MessageNext Message »
From:Hardi OK Date:April 28 2006 1:13am
Subject:Re: HELP --- Slow SP
View as plain text  
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 <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:
>
> <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: dkasak@stripped
> website: http://www.nusconsulting.com.au
>

Thread
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