CODEWITHSUNDEEP
mysqlindexingfieldunique
TomSawyer
TomSawyer

Reputation: 3819

MySQL: unique field needs to be an index?

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?

Upvotes: 59

Views: 18595

Answers (3)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57690

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.

Upvotes: 85

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

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)

Upvotes: 11

Eugen Rieck
Eugen Rieck

Reputation: 65332

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

Upvotes: 16

Related Questions