You may need to supply a bit more information - table formats, query string, output from
EXPLAIN - to allow more detailed diagnosis.
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?
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
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,
That's pretty strange. Anyway, what output do you get if you put
'explain ' in front of your queries? Are the indexes being used?
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
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.