Hi German,

Well, this isn't exactly the right list - should have been
firebird-support@yahoogroups.com, but it's closer than the
developers list.

>One question .. If I create two fields, first with not null and indexed
>unique, second field not null. And create Primary Key with frist and
>second field with this order. The second field are used for FK with
>other tables. First field always get an unique numeric value from one
>gen_id. All fields are integer.

I'm confused a bit here. You've got two fields, one unique, one
not, both not null, right?

You combine the two to make the primary key for the table, although
the first alone has all the characteristics of a primary key, right?

When you say "The second field are used for FK with other tables" do
you mean that you reference that field when creating foreign keys
from other tables? Or do you mean that the second field references
other tables? In SQL terminology, is the field in the primary key
a referencing field or a referenced field?

>When.. I search one record with there fields order. It's most slow ?

If you have an index or primary key that consists of two or more
segments, the engine will use that index if you ask for a record
restricting the query by the first segment, or the first and second,
or first, second, and third, etc. It can not use the index if you
supply values for the second segment of the key but not the first.

>What is the correct order for maximize relations/transactions speed with
>index ?

For index density, put the most selective field last. For general
usefulness, put the most frequently used field first. In this case,
why are you including the second field in the primary key at all?

>And I see .. If change order of any index, to "desc", system (firebird)
>create the same index table and index data, but neg first all bits. It's
>true ?

Yes, essentially. There are a couple of differences having to do with
nulls, but essentially, it negates the key value for descending indexes.

> If firebird use index with "stack pointers" (first
>in first out) .. to create table index and for desc indexes use inverse
>"stack pointers" (first in last out) It's not a two index orders ?

I'm not sure what you mean. Firebird uses a variant of btrees for
indexes.

> Firebird negate bit to bit, data pointer to search in desc
>indexes ?

Not the data pointer, but the key value. All indexes are searched
top down, left right. A descending index is built by negating key
values so that search goes from high to low. Building an index
key from a data value is somewhat complicated, but the simple way
to understand it is that the index key is a version of the stored
value adjusted so that a bytewise gives the desired order.

Regards,


Ann