8

Suppose I have a table with an id column and and I am maintaining unique values manually while inserting and updating records instead of creating a unique key on that column (e.g. by using a date & time for the id column value, such as 201505061648). I am not using any index or key in my table. So, does it affect query performance when I execute a select query with a where clause even if the cardinality is 100% for the id column?

select * from myTable where id=15

I mean does a unique constraint help to improve query performance or does it just force the user to maintain the record uniquely, so that SQL will get higher selectivity on the unique constraint column?

I don't know much about unique constraint and unique index. The answer given on this question helps in understanding the difference between UniqueIndex and UniqueKey. I want to know more about unique key and how it can improve my query performance.

Improve this question

3 Answers 3

Reset to default
19

Since the Stack Overflow post you linked to explains how the unique key constraint is implemented using a unique index, so the two are the same for practical purposes, I'll skip that part and try to explain how a unique index improves query performance.

Let's stick to your example where table named myTable has a column named id which happens to be filled with unique values, but no unique index on that column. Let's also suppose that the table has another column, anotherCol.

Now someone comes and issues a query like

select * from myTable where id = 15

The optimizer must decide how to find all the rows satisfying the condition id = 15. It may know, from the statistics, that values in the column are unique (num distinct = num rows in the table), however, that doesn't provide any information on where the row with id = 15 is physically located on the disk. So, the database has no choice but to scan the entire table to find the matching row. It can't even stop once it finds the first row with id = 15, since there's no guarantee that there aren't more such rows.

Now, we create a unique index on column id, and repeat the same query.

Things are radically different now. The optimizer now knows that there are only two possibilities: either there is exactly one row that satisfies the condition, or there are no such rows. So, a cheap index seek is all that is needed to find the required row if it exists; the same index seek will return no results if there are no such rows. So, the query will run fast.

Suppose there is also a (normal, non-unique) index on anotherCol, and you issue a query like

select * from myTable where id = 15 and anotherCol = 100

The optimizer can now choose between using the index on id, index on anotherCol, and a table scan. It will choose the most selective method, because it requires the least work to get to the results - so it will use the unique index, again.

In general, whenever your query has where clause predicates that are joined with AND (a common case) and any of the columns in the where clause has a unique index on it, the optimizer will chose that index as the access method and the query will run fast.

The only case when the unique index will not be used is when it cannot be used, for example when a query like 

select * from myTable where id = 15 or val = 100

is issued. Since the condition between the predicates is OR, the fact that we can find the row where id = 15 fast doesn't mean much since we still have to find other rows where val = 100. If val were not indexed, a table scan would be the only solution (rendering unique index seek useless, as the row where id = 15 would be picked up by the table scan anyway); since val is indexed, the optimizer might opt to use both indexes to find rows satisfying each condition individually and then concatenate the results.

Hopefully this makes things a bit clearer to you.

Improve this answer
2
  • 5
    +1, with a small note. In your first example with a unique index, the seek isn't quite the only thing necessary: because there are other fields in the query, the database must also do a key lookup to retrieve the record, after finding it in the unique index. Unless the unique index is also the table's clustering index, in which case the data will be stored with the index.
    – Jon of All Trades
    May 6, 2015 at 20:37
  • 2
    Yes, you are right. I left that out because it didn't seem that important for the argument.
    – zgguy
    May 7, 2015 at 4:42
6

The optimizer is able to use the knowledge that a column is unique when deciding how to retrieve rows predicated on that column. It may produce a different, more efficient, execution plan with the unique constraint in place than without it. Without the unique constraint the optimizer has to interpolate from the statistics object created on that column, which is a perpetual source of execution plan problems.

Of course the index created to enforce the unique constraint is a great source of performance improvement, too.

Improve this answer
2

A unique constraint will create an index.

So, when in your query there is:

where id=15

An index will drastically improve your query. With no index you will read all the table.

Improve this answer
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.