2

This describes different indexes:

KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any restraints on your data so they are used only for making sure certain queries can run quickly.

UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.

I understand the benefit to application logic (you don't want uniqueness check) but is there also a performance improvement? Specifically, how much faster are writes using INDEX instead of UNIQUE?

Community
  • 1
  • 1
lf215
  • 1,185
  • 7
  • 41
  • 83

2 Answers2

3

UNIQUE KEY is a constraint, and you use it when you want to enforce that constraint.

KEY is an index, which you pick to make certain queries more efficient.

The performance of inserting into a table with either type of index is virtually the same. That is, the difference, if any, is so minor that it's not worth picking one over the other for the sake of performance.

Choose the type of index to support your constraints. Use UNIQUE KEY if and only if you want to enforce uniqueness. Use KEY otherwise.

Your question is like asking, "which is faster, a motorcycle or a speedboat?" They are used in different situations, so judging them on their speed isn't the point.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • My intuition is that there is very little performance improvement. The dbms has to find a place in the index tree to put the index record in both cases. Only difference is that with UNIQUE it errors out if there are already other records in that index for that value. Is this correct? – lf215 Mar 02 '18 at 16:11
  • Yes, a non-unique index allows you to store multiple rows with the same value. A unique constraint uses an index to find out if there is already a row with the value. – Bill Karwin Mar 02 '18 at 16:13
  • UNIQUE KEY also makes SELECT queries faster it's just a index with a special feature/function (called a constraint) enforcing uniqueness.. – Raymond Nijland Mar 02 '18 at 16:20
1

INSERT

When a row is inserted, all unique keys (PRIMARY and UNIQUE) are immediately checked for duplicate keys. This is so that you get an error on the INSERT if necessary. The updating of non-unique INDEXes is delayed (for discussion, see "Change buffering"). The work will be done in the background so your INSERT won't be waiting for it.

So, there is a slight overhead in UNIQUE for inserting. But, as already pointed out, if you need the uniqueness constraint, then use it.

SELECT

Any kind of index (PRIMARY, UNIQUE, or INDEX) may be used to speed up a SELECT. Mostly, the types of index work identically. However with PRIMARY and UNIQUE, the optimizer can know that there will only one (or possibly zero) rows matching a given value, so it can fetch the one row, then quit. For a non-unique index, there could be more than one row, so it keeps scanning the index, checking for more rows. This scan stops after peeking at the first non-matching row. So, there is a small (very small) overhead for non-unique indexes versus unique.

Bottom Line

The performance issues are less important than the semantics (uniqueness constraint vs. not).

Rick James
  • 135,179
  • 13
  • 127
  • 222