Menu
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?
939
In theory there is a slight difference in update performance as the engine needs to enforce uniqueness in a unique index, but in reality this is one going to be at most a few CPU cycles per row difference so will be unnoticeable. Only create a unique index for values that you know should be unique.
In addition to enforcing the uniqueness of data values, a unique index can also be used to improve data retrieval performance during query processing. Non-unique indexes are not used to enforce constraints on the tables with which they are associated.
If you feel like your data should be UNIQUE , use a unique index. You may think it's optional (for instance, working it out at application level) and that a normal index will do, but it actually represents a guarantee for Mysql that each row is unique, which incidentally provides a performance benefit.
Index: It is a schema object which is used to provide improved performance in the retrieval of rows from a table. Unique Index: Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns).
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.
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
