If I apply the "UNIQUE" constraint to a column, will the database engine traverse the whole table for an occurrence thus affecting performance?

Question

If I have a table with millions of entries, will the engine have to check every field in the column to see if the input matches one of the fields?

How does this work?

It will do index seek(very efficient). [Does a UNIQUE constraint automatically create an INDEX on the field(s)?](https://stackoverflow.com/questions/9764120/does-a-unique-constraint-automatically-create-an-index-on-the-fields#9764392) — Lukasz Szozda, Jul 04 '18 at 05:58
https://dba.stackexchange.com/questions/144/when-should-i-use-a-unique-constraint-instead-of-a-unique-index — P.Salmon, Jul 04 '18 at 06:02

score 1 · Accepted Answer · answered Jul 04 '18 at 12:29

No. A unique constraint creates a unique index. The index is used for validating the constraint. The index can also be used for other purposes as well.

As far as I know, all databases, including MySQL, implement such an index using a B-tree. A lookup is O(log(n)) where n is the number of records in the table.

The difference between a unique index and unique constraint is cosmetic. A violation of a unique constraint returns the constraint name; a violation of a unique index returns the index name.

If I apply the "UNIQUE" constraint to a column, will the database engine traverse the whole table for an occurrence thus affecting performance?

1 Answers1