MySQL Forums
Forum List  »  General

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)

Options: ReplyQuote


Subject
Written By
Posted
Re: Issues with possible duplicate indices on table
December 11, 2011 06:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.