Dear All,
Just to get a better understanding of how indices work in MySQL - If I
have a Innodb table with a composite primary key (fld1,fld2,fld3,fld4,fld5),
then my understanding is that MySQL optimizes just the leftmost primary key
(fld1 in this case).
Hence a query like select * from tbl1 where fld2 > 900 would result in a
full table scan even though it's part of the composite key but select *
from tbl1 where fld1 > 900 would be extremely quicker since it would search
based on Index pages.
Is my understanding correct? If so, how can we get around this issue ? In
real-life databases you will always run in cases where you end up making a
composite key on table. One possible solution would be to create non-unique,
non-primary index on each of fld2,fld3,fld4,fld5 but then the inserts would
be horribly slow hence was wondering if I am totally missing a very clean
solution to the whole issue.
Your kind help would be greatly appreciated!
Regards
Manoj
