MySQL Forums
Forum List  »  Optimizer & Parser

does unique index do more than just being a constraint?
Posted by: Martijn Groen
Date: April 24, 2009 08:29PM

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 :)

Options: ReplyQuote

Written By
does unique index do more than just being a constraint?
April 24, 2009 08:29PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.