Adam wrote:

> FKs are implemented using an index. Obviously there is an overhead
> when inserting, updating or deleting records. No worse than the
> overhead of any other indexed fields. Almost always, foreign keys will
> improve select performance.

I agree that foreign keys are good and ought to be used (I use them far
to rarely). However, this last sentence ('Almost always') must not be
understood as meaning that people could just add foreign keys without
testing existing queries.

In general, if there already exists an index on the same field as the
foreign key is defined, then this existing index should be deleted
(Firebird has/had problems with identical indexes and they are at best
unnecessary) and further testing ought not to be necessary. I haven't
heard of ordinary indexes and indexes defined through foreign keys to be
treated differently by the optimizer, so the only case I can think of
where doing something like this may affect an existing query, is when
the plan is written in the query itself (which is possible, but not
recommended with Firebird).

Foreign keys on fields that aren't indexed already, may make existing
queries quicker, slower or not affect performance at all. If no thought
has been made as to which indexes to define and no-one has ever thought
about performance, then foreign keys are likely to improve the situation.

However, if a lot of thought have gone into defining indexes and plans
and queries carefully examined to squeeze the best possible performance
out of them, then adding a foreign key is more likely to slow down
existing queries than to speed them up.

Set