--- In firebird-support@yahoogroups.com, "Robert DiFalco"
<rdifalco@t...> wrote:

> This is all with FB 1.0x.
>
> 1. If I have a set of unique values indexed, does making the INDEX
> explicitly UNIQUE change the performance of inserts at all? Or does

it

> just add an explicit constraint to keep the index unique? Didn't

know if

> adding UNIQUE maybe allowed Firebird to use a more optimal BTree

page

> structure.
>

It really depends on your business logic. If the field must be
unique, then add a unique constraint to the table. If you need that
field indexed to improve performance, add a unique index to it. Of
course adding a unique constraint slows down inserts and updates to
that field. But if you add a unique constraint, it is not because you
are bored one day. It is because your business logic tells you that
bad stuff may happen if there are two identical values in that field.

> 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?
>

Making the index compound can help the optimiser by allowing the
index to use even more rows.

So if tableA has an index on Field1,Field2

select *
from tableA
where Field1=1
and Field2=3

would be a very efficient operation.

That same index could not help you though on the following query

select *
from tableA
where Field2=3

Creating Indexes (I hate calling them that - should be indices!!!)
should be a deliberate operation. There is obviously some overhead in
inserting/updating and deleting data because the index(es) need to be
maintained. However, the same index could benefit your delete
operation if it allowed the optimiser to identify the right record(s)
quickly.

> 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. IOW, if I

have

> an index on FOO, BAR that I don't need a separate index on FOO.

However,

> I have found that with certain joined queries that the optimizer

will

> use the index on FOO if it exists but will not use the FOO+BAR

index.

> Any thoughts?
>

The index on Foo will be smaller than the compound index on Foo +
Bar. IOW, it can traverse it faster and so is normally a better
choice. If the optimiser gets it wrong (which it occasionally does,
but not very often IMO), you can always use the PLAN directive to
tell it how you want it to execute.


Hope that helps

Adam