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.