| Subject | Re: [firebird-support] how work an index.. |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-11-29T22:42:51Z |
At 10:43 PM 29/11/2004 +0100, you wrote:
you should not design hierarchical primary keys on child tables. The first
field, alone, should be the primary key. The second field, alone, should
be the foreign key to the parent.
It can be bad for performance to have duplicate indexes on a
field. Firebird creates a unique index automatically for a primary key. To
solve your problem, remove the unique index from the first field and change
the primary key to be the first field alone.
selection. However, again, you need to stop Paradox-thinking. In
Firebird, you do not have to design indexes or keys for sorting. If an
index exists that would be useful, the optimizer will choose it. Sometimes,
indexed sorts are actually slower than natural sorts. It's more important
to consider direction (ASC or DESC).
design appropriate indexes.
Always be aware that mandatory indexes are created automatically for
PRIMARY KEY and FOREIGN KEY constraints. Never allow duplicate indexes to
co-exist.
I couldn't work out what you were saying about ASC and DESC indexes. The
main thing to understand is that all indexes in Firebird are
uni-directional. An ASC index (the default) is different to a DESC
index. An ASC index cannot be used for a descending sort or search and a
DESC index cannot be used for an ascending sort or search. If you need
both, define both.
./heLen
>Hi to allThis looks like a legacy from Paradox. Firebird uses true Foreign keys so
>
>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.
you should not design hierarchical primary keys on child tables. The first
field, alone, should be the primary key. The second field, alone, should
be the foreign key to the parent.
It can be bad for performance to have duplicate indexes on a
field. Firebird creates a unique index automatically for a primary key. To
solve your problem, remove the unique index from the first field and change
the primary key to be the first field alone.
>When.. I search one record with there fields order. It's most slow ?The order of multi-segment keys does matter, in terms of index
>What is the correct order for maximize relations/transactions speed with
>index ?
>I think.. order of fields and order of primary key definition, is not a
>problem...
selection. However, again, you need to stop Paradox-thinking. In
Firebird, you do not have to design indexes or keys for sorting. If an
index exists that would be useful, the optimizer will choose it. Sometimes,
indexed sorts are actually slower than natural sorts. It's more important
to consider direction (ASC or DESC).
>I think.. the system (firebird) use apropiate index for any SQLIt tries to use an appropriate index, but it cannot do much if you don't
>transaction.
design appropriate indexes.
Always be aware that mandatory indexes are created automatically for
PRIMARY KEY and FOREIGN KEY constraints. Never allow duplicate indexes to
co-exist.
I couldn't work out what you were saying about ASC and DESC indexes. The
main thing to understand is that all indexes in Firebird are
uni-directional. An ASC index (the default) is different to a DESC
index. An ASC index cannot be used for a descending sort or search and a
DESC index cannot be used for an ascending sort or search. If you need
both, define both.
./heLen