does unique index do more than just being a constraint?

[Martijn Groen]

I’ve been trying to get some clarity around some of the technical aspects of using unique index.

The MySQL reference manual states;

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

I've got a table with a column called file_name (using inodb table storage engine). This column must be unique so I’ve applied a unique index to it which suites this purpose.

I also need to be able to search the column file_name very quickly so I’ve also applied a normal index to it. So that’s two indexes on the same column.

The unique index acts as a constraint but does it also speed up search? Is there still any purpose for me having the second normal index on the column?

I couldn’t find anything in the documentation that eluded to anything other than a unique index purely acting as a unique constrain and nothing more.

Thanks, Martijn :)