MySQL Forums
Forum List  »  General

Re: Advice on redundant indexing
Posted by: Rick James
Date: April 02, 2009 12:51AM

It depends.

Normally, when I see both of these
INDEX(a)
INDEX(a,b)
I say get rid of the first because it is "covered" by the other.

But you have
UNIQUE(a)
INDEX(a,b)
Removing the former would lose the uniqueness constraint. (PK is UNIQUE.)

INDEX(b)
INDEX(a,b)
Serve different functions. The former lets you search for a "b"; the latter does not.

UNIQUE(a)
INDEX(b)
INDEX(a,b)
Could possibly be transformed into
UNIQUE(a)
INDEX(b,a)
because (b) is covered by (b,a). But you would lose efficiency in these cases:
WHERE a=123 AND b > 234
WHERE a=123 ORDER BY b
Only (a,b) can provide the range scan or ORDER BY for b; (b,a) cannot without losing the test on 'a'.

Bottom line... You might have the optimal set of indexes. I cannot tell without seeing your queries.

(Your set of indexes are just elaborations on the above set.)

Options: ReplyQuote


Subject
Written By
Posted
Re: Advice on redundant indexing
April 02, 2009 12:51AM


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.