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