MySQL: unique field needs to be an index?
Every UNIQUE
field is by definition indexed with a UNIQUE INDEX
- this also happens to be the fastest searchable access path.
If the field needs to be UNIQUE then it should either be the PRIMARY KEY
or a UNIQUE INDEX
.
As for performance between UNIQUE INDEX
and INDEX
, there is no difference when selecting as both will use the same algorithm i.e. hashing or b-tree. It's just that with a UNIQUE
index, especially a numeric i.e. INT
one, it will be faster than an index which contains duplicates as algorithms such as b-tree are able to more efficiently get to the requested row(s)
No, you dont have to index it again. When you specify UNIQUE KEY
, the column is indexed. So it has no difference in performance with other indexed column (e.g. PRIMARY KEY) of same type.
However if the type is different, there will be little performance difference.