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
