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.

Answer 1

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.

Answer 2

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).