MySql unique index vs. index speed

Question

In Mysql, Other then data integrity - Are there any performance benefits to using a Unique Index over a regular index? (assuming the data IS unique)

i.e. - will it take less time to create? update? or query a unique index over a regular one?

Answer 1

The query optimizer can use a unique index more effectively for certain queries than it can use an ordinary index. For just one example, in a SELECT DISTINCT query that includes all the columns of the unique index, the query optimizer can emit a plan that skips sorting the results and eliminating duplicates -- even if the plan doesn't explicitly use the index!

Generally speaking, though, the performance impact of a unique index vs. a non-unique one on the same columns is dependent on your queries.

My advice is to model your data as accurately as possible. If it is a characteristic of your data that a certain combination of columns will not be duplicated in different rows, AND you intend to index those columns, then the index should be a unique index.

Indeed, in such a case you should consider a unique index for the purpose of enforcing the uniqueness of those columns, even if you weren't otherwise going to index them. Adding an index does add a bit of overhead to insertions, deletions, and some updates, but unless your performance for those operations is unsatisfactory it's probably best to ignore that.