Decision to use KEY or UNIQUE KEY

Question

I understand that UNIQUE KEY is a unique index and KEY is a non-unique index. I have read that in case of unique index'es inserting data might result in some IO.

If we don't have to rely on the DB for unique-ness and we still want fast lookup's using column 'b' would you suggest to use a non unique index (KEY) instead of a unique index (UNIQUE KEY)?

Answer 1

Both unique and non-unique indexes result in I/O operations for INSERT, DELETE, and UPDATE statements. The amount of index overhead should be pretty much the same. The difference is that unique indexes might result in a failure of an INSERT or UPDATE under normal use (of course, the operations might fail for other reasons, such as disk being full, but that is an unusual circumstance).

I don't understand this statement: "If we don't have to rely on the DB for unique-ness". A UNIQUE attribute in a table is a description of the column/columns that comprise the key. One of the functions of a database is to maintain the integrity of the data, so let the database do what it is designed for.

As for performance, I don't think there is a significant difference between unique and non-unique indexes. Unique indexes may be slightly more optimized for certain operations because the compiler knows that a single lookup returns only one row. The difference between an index lookup and an index scan that returns one row is probably pretty small in practice.