If your data are unique, you should create a UNIQUE index on them.
This implies no additional overhead and affects optimizer’s decisions in certain cases so that it can choose a better algorithm.
In SQL Server and in PostgreSQL, for instance, if you sort on a UNIQUE key, the optimizer ignores the ORDER BY clauses used after that (since they are irrelevant), i. e. this query:
SELECT *
FROM mytable
ORDER BY
col_unique, other_col
LIMIT 10
will use an index on col_unique and won’t sort on other_col because it’s useless.
This query:
SELECT *
FROM mytable
WHERE mycol IN
(
SELECT othercol
FROM othertable
)
will also be converted into an INNER JOIN (as opposed to a SEMI JOIN) if there is a UNIQUE index on othertable.othercol.
An index always contains some kind of a pointer to the row (ctid in PostgreSQL, row pointer in MyISAM, primary key/uniquifier in InnoDB) and the leaves are ordered on these pointers, so in fact every index leaf is unique is some way (though it may not be obvious).
See this article in my blog for performance details:
- Making an index
UNIQUE