17

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?

epeleg
  • 10,347
  • 17
  • 101
  • 151
  • You can take into account the following considerations: http://stackoverflow.com/questions/1293499/are-unique-indexes-better-for-column-search-performance-pgsql-mysql – AntonBerezin Jan 27 '15 at 15:52

1 Answers1

23

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.

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
  • so for unique indexes it forces every value in a column to be unique for each row?? – oldboy Oct 22 '17 at 09:32
  • 1
    Yes, @Anthony, that's what the "unique" part of "unique index" means. And the "index" part means that the database maintains extra information to help it swiftly associate values of the indexed column(s) with their corresponding row. – John Bollinger Oct 22 '17 at 19:02
  • thanks. one other question: if i'm constantly querying a handful of columns for every row, i should be indexing this correct? – oldboy Oct 23 '17 at 00:41
  • 1
    @Anthony, Indexes are usually the most effective when they can support your `WHERE` conditions and / or join predicates. Although sometimes an index can provide some advantage simply by covering all the columns being selected, that's rarely a good strategy for choosing what indexes to define. Remember that generally, at most one index will be used for each table in your query. Remember also that many factors affect query performance, so you need to test, and sometimes to tweak. – John Bollinger Oct 23 '17 at 04:31
  • thanks! it actually wasn't even able to do it. the size of the index was too big iirc – oldboy Oct 24 '17 at 21:19
  • 5
    It's a well written response but "it depends" is a very weak answer. – Qubei Dec 08 '17 at 06:03
  • 3
    @Qubei unless you live under a rock - "It depends" is the most truthful point of any good answer. Because everything is relative the correct solution always depends on the circumstances of each person's particular problem. – Sir Rogers Jul 09 '18 at 14:20
  • Kudos @JohnBollinger for the great answer! – Sir Rogers Jul 09 '18 at 14:20
  • Tell me more about these circumstances on which it depends please @SirRogers and how they effect it. I'm not sure how "it depends... on circumstances" with no evidence or further information possibly satisfies the question. – Qubei Jul 11 '18 at 08:41
  • Inasmuch as you have not accepted my invitation to write your own answer, @Qubei, I am left uncertain how you imagine this answer could be improved. – John Bollinger Jul 11 '18 at 17:56
  • @JohnBollinger actually some EXPLAIN examples would be nice, thanks! :) – Anubioz Oct 28 '19 at 04:18
  • 1
    @JohnBollinger as far as I have tested, explain select distinct concat(val1,val2) with unique index val1,val2 shows just the same result as with non-unique index val1,val2. So either unique index is useful just for enforcing data uniques or my queries are not those which gain performance... – Anubioz Oct 28 '19 at 04:27