Mysql: Unique field need to be index?

Question

I have one unique field in my table, and I need to search over it quickly. Do I need to index it?

Do searches over unique fields and indexed fields vary in speed or resource usage?

Answer 1

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.

Answer 2

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)

Answer 3

Every UNIQUE field is by definition indexed with a UNIQUE INDEX - this also happens to be the fastest searchable access path.