Are UNIQUE SQL indexes also used for speeding up searches?

Question

Suppose a SQL database of "values of series". Each Value belongs to a Series, and has a date:

@Entity
class Series { … }

Value {
   Series series;
   Date date;
   …
   }

Each value is unique for each combination of series and date, which is guaranteed by this index:

UkSeries UNIQUE INDEX value (series ASC, data ASC)

In Hibernate, the above index is created by this annotation:

@Table (
    uniqueConstraints = @UniqueConstraint (columnNames = {"series", "data"}))

Now, please compare it to this possible alternative index definition:

UkSeries UNIQUE INDEX value (series ASC, data ASC)
IdxSeries INDEX value (series ASC, data ASC)

@Table (
    uniqueConstraints = @UniqueConstraint (columnNames = {"series", "data"}),
    indexes = { @Index (name = "IdxSeries", columnList = "series, data") })

Considering I also want to speed up the search for values in the database, my question is:

Will the "UNIQUE INDEX" alone, apart from guaranteeing uniqueness, also be used to speed the searches? Or do I really need both indexes, as presented in my alternative index definition above?

Note: I am using MySQL, but I believe this question is simple enough so that my specific database doesn't matter.

I don't have any specific MySQL knowledge, but generally, that second index there will be entirely redundant. — 500 - Internal Server Error, Jun 26 '17 at 19:55
Good question. I'm pretty certain the answer is you only need the unique index, but I don't know offhand the mysql documentation so I'll let someone else answer. — Alvin Thompson, Jun 26 '17 at 19:56

score 6 · Accepted Answer · answered Jun 26 '17 at 20:06

6

This answer explains that a unique constraint in MySQL (like in most RDBMSs) is simply a special type of index, so you don't need both. The unique constraint will do.

answered Jun 26 '17 at 20:06

Alvin Thompson

5,388
3
26
39

For empirical evidence to support this try the following experiment: create just the unique constraint in the table, then in another table reference the fields in the unique constraint as a foreign key (with the fields in the same order). Since this is allowed--and it doesn't create a new index--it's pretty strong evidence a unique constraint is also an index. – Alvin Thompson Jun 26 '17 at 20:30
1

I know unique constraints are indexes, but I was unsure if it's some kind of index that prevents it being used for regular searches. And, at least in MySQL, my alternative index definition is creating both indexes, one marked UNIQUE and the other not, so if anything this would be evidence they are NOT the same. That's why I had to ask this question. However, if I do `@Index (columnList = "series, date", unique = true)` it actually creates an index marked with UNIQUE that seems to be the same as the unique constraint. So I guess they are the same but MySQL simply doesn't check the redundancy. – Marcelo Glasberg Jun 26 '17 at 22:21
@MarcG: Your original question asks if unique constraints can be used as indexes for the purposes of speeding up searches, and not if the two types of index are 100% the same–obviously they are not. In my answer I neither stated nor implied that they were identical–I stated that unique constraints are a type of index, which they are (and that answers the question you actually asked). If you’re now claiming to be asking if they are identical, please update the question accordingly. However, I don’t know why you would ask that since you also stated it was obvious they were not identical. – Alvin Thompson Jun 27 '17 at 01:19
I know both are indexes. I know they are not identical. I just wanted to be sure that a unique key is a "special type of index" that can be used to speed up query searches. – Marcelo Glasberg Jun 27 '17 at 02:25
Then my first comment still applies. – Alvin Thompson Jun 27 '17 at 11:49

Zdeněk Jelínek · Answer 2 · 2017-06-26T21:01:26.543

TL;DR: It depends if the searched fields are a part of the index. The non-unique index is completely redundant and may actually slow inserts/updates/deletes.

Generally speaking, the main goal of indexes is to increase search performance.

In order to adhere to uniqueness constraint on a field during an insert or update, the RDMS needs to check whether a duplicate value doesn't already exist in the table. And that means search. It is therefore natural that UNIQUE constraint is also an index (to speed up that duplicate search) and may also be used for searching or at least limiting the intermediate result set of other queries.

In addition to speeding up searches, indexes may also slow down inserts/updates/deletes. Indexes are duplicates of the information already stored in the table and need to be up to date as well.

Besides modeling the natural restrictions of the contained data, indexes should be used based on the application's data demands - to speed up slow searches and not to slow down updates.

Creating a unique index may or may not speed up searches. That depends if the searched fields are a part of the unique index (or related to those fields via additional constraints).

Are UNIQUE SQL indexes also used for speeding up searches?

2 Answers2