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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
dotancohen
  • 1,085
  • 6
  • 16
  • 27

1 Answers1

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

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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 Jan 07 '14 at 21:43