Re: Issues with possible duplicate indices on table
Posted by:
Rick James
Date: December 11, 2011 06:27PM
INDEX(a,b)
INDEX(a) -- This is almost always a waste.
A PRIMARY KEY is a UNIQUE KEY.
InnoDB is "clustered" on the PRIMARY KEY (ID in your case). So
INDEX(ID, anything) or
UNIQUE(ID, anything)
is totally wasted.
Fields with low cardinality (flags, status, etc) are rarely useful, unless combined with other fields.
We can't judge the utility of the indexes without seeing the SELECTs that might use them.
MySQL never (almost never) uses more than one index in a single query. So,
INDEX(a), INDEX(b)
is not likely to be useful when testing against both a and b. One of these _might_ work better:
INDEX(a,b)
INDEX(b,a)