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?
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
> website: http://www.nusconsulting.com.au