Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Posted on

Question :

Is there any different how mysql treats

UNIQUE (column1, column2),
INDEX (column1)

and

UNIQUE INDEX (column1, column2)

and

UNIQUE (column1, column2)

In the latter, I think mysql will automatically creates index for UNIQUE to avoid duplicate records. Am I right?

Answer :

UNIQUE (column1, column2) implies UNIQUE INDEX (column1, column2) because the INDEX keyword is optional. So an index is created. However, the MySQL 5.5 docs show that the INDEX (or KEY) keyword is mandatory so UNIQUE (column1, column2) should give an error

INDEX (column1, column2) does not mean UNIQUE INDEX (column1, column2): it means an index that does allow duplicate (column1, column2) pairs

The second and and third are equivalent.

The first is like the third, but adds a (non-unique) index in addition to the unique index. Which is unnecessary and wasteful.

Leave a Reply

Your email address will not be published. Required fields are marked *