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 >
