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