Robert DiFalco wrote:

>
> 1. If I have a set of unique values indexed, does making the INDEX
> explicitly UNIQUE change the performance of inserts at all?

A unique index requires slightly more handling than a non-unique index,
but the difference is probably not measurable except in tests designed
for that specific purpose. Adding a UNIQUE constraint creates a unique
index, so if you use a constraint, don't bother with defining an index.

Unique indexes (as in "CREATE UNIQUE INDEX <blah>...) are a holdover
from the time before SQL constraints. If you want to make something
unique, a UNIQUE constraint is the best way to do it. (Better than
creating a unique index and much better than creating a trigger).

> 2. If I want to index on a column with a lot of dupes and I have a
> unique numeric column in that field, what does it buy me to make this
> index compound and add the unique numeric column as the second key in
> the index? Does it impact query or insert performance or does it just
> impact the performance of deletes?

Yes, that's a good thing to do on any table that will have significant
delete activity or will have that key value modified often. Adding the
second part to the index key increases the size of the index, making
retrievals slightly slower, so don't bother making the key more unique
on tables that are purely insert/read.

>
> 3. I've heard that Firebird will always use the first key of a compound
> index if there is not a separate index for the first key.

It can use that index. In version 1 and 1.5, it doesn't have a good
idea of the selectivity of a partial key, so it tends to underestimate
the value of that key. Arno Brinkman has added selectivity information
for V2.0 indexes that will improve the use of partial key matches.

Regards,


An