Re: Is there any performance reason to use "unique index"

[Harrison]

A unique index can actually be faster than a regular index.  The reason 
is that MySQL knows that there can only be a single matching row for 
each value.  In particular, this allows you to get "const" and "eq_ref" 
for the type in an EXPLAIN, which are two of the fastest methods of 
table access.  Even if you aren't use these access methods, it will 
never be any slower to access than a regular index.

Keep in mind that it will take longer to build the index in the first 
place, and make your decision appropriately.

Regards,
Harrison
On Thursday, September 9, 2004, at 05:01  PM, [EMAIL PROTECTED] wrote:
The uniqueness constraint would only be enforced during an INSERT or an
UPDATE. If your table is read-only, declaring the index as UNIQUE will 
be
overkill.  I can't tell you about any kind of performance hit during
reading but I try to follow the maxim "don't ask for it if you won't 
need
it". I would use just a straight index.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Wesley Furgiuele <[EMAIL PROTECTED]> wrote on 09/09/2004 04:36:50 
PM:

Hi:
I was wondering if there is any performance-related reason to use a
unique index versus a standard index? Is the only benefit of a unique
index that it will prevent duplicate values from being inserted into a
table unless explicitly allowed?
I have a column, colA, that I know contains only unique values because
I create the table using a 'GROUP BY colA' clause. Before I use the
table for any more work, I want to index colA. This table will have no
further rows added to it, so I don't need to worry about a potential
duplicate value being inserted. I was just wondering if it was a
performance gain/hit to use a unique index, or if the difference was
negligible.
Thanks.
Wes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]