I am auditing an application which I've been developing over the course of several weeks. I've noticed this in CREATE TABLE
:
UNIQUE KEY `baz_3` (`baz`,`foo`,`bar`),
KEY `baz` (`baz`),
Is the explicit KEY
redundant, as I think it is? I just want to make sure before I DROP
it. From what I've read I do believe that the UNIQUE KEY
will act as an Index when the application performs WHERE baz='something'
but MySQL seems so intricate that I find it prudent to ask here first.
Best Answer
Yes, you can drop it because
baz
andbaz_3
have identical leading columns. This scenario is known as having redundant indexes.However, be careful that the key
baz
is not being used to support a foreign key constraint if the table is InnoDB. See my post Do I have duplicate key indexes?If the table is MyISAM, go ahead and drop it with confidence. The MySQL Query Optimizer will use the UNIQUE KEY
baz_3
for query optimization in its execution plans.If you want to locate all tables with redundant indexes, see this post : MySql - find redundant indexes