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)?
Best How To :
Both unique and non-unique indexes result in I/O operations for
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
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.