2

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.

1 Answer 1

2

Yes, you can drop it because baz and baz_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

1
  • Thank you Rolando, it is exactly for this type of info (in the links) that I came here. In fact, the table is InnoDB but it is not (currently) using foreign keys.
    – dotancohen
    Commented Jan 7, 2014 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.