Hi,
is a unique constraint an index by default? If not, does a unique constraint has the same performance results as an indexed column when using it in the "select .. where" clause?
thanks
+2
A:
A constraint is actually very different from an index: It just says that MySQL is supposed to enforce uniqueness for you. An index however (although it can be unique) is about the physical ordering on your harddisk, or about additional structures (usually a tree) that allows for efficient searching on the column.
You might however be confusing all this with primary keys which define a (usually clustered) unique index.
dionadar
2009-08-01 21:46:39
so lets say in my table I have a column "email", and I do several times the query "select * from tbl where email like 'something'". This column is a unique column, however it is not the primary key. So would it be better for performance that I index it?
2009-08-01 21:57:41
@Kai: Yes, an index will improve performance when reading data (writing data can be hindered). A unique constraint only means that for all the rows in the table, the values in the columns with unique constraints defined for them will be unique - no two email columns values would be alike.
OMG Ponies
2009-08-01 22:12:47
+2
A:
A unique constraint is necessarily an index. You normally define it as "UNIQUE INDEX". An index would be required in any case to efficiently implement a unique constraint, so having one is no disadvantage.
MarkR
2009-08-01 21:58:25
+1
A:
A unique constraint is a way for expressing the fact that something (like some combination of attribute values) must be unique within the scope of an entire relation ("table")).
That is at the level of LOGICAL design.
An index is a possibly useful means in helping to enforce such a constraint.
That is at the level of PHYSICAL design.
Some DBMS products might infer certain physical design constructs, such as the presence of some index, from the presence of certain logical design constructs, such as a UNIQUE constraint. Others might not.
A:
UNIQUE is actually a constraint on an index, so yes, UNIQUE implies that there is an index on the field you're enforcing uniqueness on.
Meredith L. Patterson
2009-08-01 22:50:08
A:
Check out the primary key constraint before you start using unique constraints.
A primary key constraint amounts to declaring a unique constraint and a not null constraint. If there is more than one column in the primary key, each column gets a not null constraint, but the unique constraint applies to all the columns taken together.
When you declare a primary key, the DBMS will create an index for you. You can drop the index if you like, but you'll get horrible performance when the DBMS does table scans to check for uniqueness.
Primary key constraints enforce entity integrity, while REFERENCES (foreign key) constraints enforce referential integrity. Together, they go a long way towards ensuring data integrity.
Walter Mitty
2009-08-02 13:15:17