Thanks Adam.
I will enforce RI using trigger while waiting for FB2 release.


----- Original Message ----
From: Adam <s3057043@...>
To: firebird-support@yahoogroups.com
Sent: Friday, September 1, 2006 12:11:54 PM
Subject: [firebird-support] Re: Low Selective Index

--- In firebird-support@ yahoogroups. com, chee hee <hee_cm18@.. .> wrote:
>
> Hi All,
>
> I have a transaction table which has about 5 million records (and
growing) and has a foreign key column to a table called Company. The
Company table only has 3 records. I understand that a foreign key will
have an index created automatically. So this would be a very low
selective index and from the book theory, low selective index is not
good for performance.
>
> So in general, should we create foreign key from very huge table (>
million records) to very small table (< 10 records) ?

What we *should* do is to define a foreign key constraint whenever
there is a foreign key relationship. The only reason we would even
contemplate forgetting the index is due to a Firebird implementation
decision.

What you may 'get away with' is to create triggers on the relevant
tables to ensure the relationship, and this will avoid the index being
created. That (hack) only works if the table referenced is static,
otherwise you get isolation problems.

Firebird 2 suffers less problems in garbage collection of such poorly
selective indices due to a more appropriate on disk structure for
indices, so when that is released your problems with the index may
well disappear.

To avoid using an index in a query, you can usually +0 to the field
(for numbers/dates, or ||'' for strings) to prevent the index from
being used, but that doesn't really help with garbage collection.

What may be useful (and I do not know if it is possible) is to have
some keyword that could go in the foreign key definition to tell
Firebird to not bother to index it, or is the index actually required
for the logic to work?

Adam




[Non-text portions of this message have been removed]