Generally speaking, indexes are not supposed to affect the results of SQL queries, aside from runtime performance.
But that’s not true for unique indexes, which are actually constraints, and can affect inserts and updates in a number of ways … and more.
With MySQL tables in particular (verify for the version and storage engine that you are using):
- unique indexes allow only one record with the same unique non-null indexed values
- unique indexes on partial columns (like varchar email(40)) allow only one record with the same value up to the length of the partial column, ignoring characters after that for the purpose of indexing.
- unique indexes allow null values to be duplicated, except in BDB tables. This can have security issues if you are relying on a unique index to enforce username uniqueness, for example.
I generally avoid nullable columns with MySQL and instead use default ” or default 0, #3 being one of many surprises with null.
MySQL manual: CREATE TABLE syntax
MySQL manual: ALTER TABLE syntax
Bug #8173: unique index allows duplicates with null values
Bug #25407: MySQL prefers UNIQUE index for IS NULL conditions over more selective conditions
