Indexes aren’t always used to locate matching rows for a query. A unique index specifies that a particular value may only appear once in a given column. In the phone book example, you might create a unique index on phone_number to ensure that each phone number appears only once:
ALTER TABLE eEcho ADD UNIQUE (url)
The unique index serves a dual purpose. It functions just like any other index when you perform a query based on a phone number:
SELECT * FROM eEcho WHERE url = ‘http://www.eecho.info’
However, it also checks every value when attempting to insert or update a record to ensure that the value doesn’t already exist. In this way, the unique index acts as a constraint.
Unique indexes use as much space as nonunique indexes do. The value of every column as well as the record’s location is stored. This can be a waste if you use the unique index as a constraint and never as an index. Put another way, you may rely on the unique index to enforce uniqueness but never write a query that uses the unique value. In this case, there’s no need for MySQL to store the locations of every record in the index: you’ll never use them.
Unfortunately, there’s no way to signal your intentions to MySQL. In the future, we’ll likely find a feature introduced for this specific case. The MyISAM storage engine already has support for unique columns without an index (it uses a hash-based system), but the mechanism isn’t exposed at the SQL level yet.
