At 12:37 AM 4/4/00 -0400, Claudio Valderrama C. wrote:

> The main point is not the FK declaration itself (I will be
> surprised if the
>optimizer knew about it), it's the index that's created automagically as
>part of the FK that confuses the optimizer. Getting rid of that index makes
>the query run much faster.

Here are some questions about foreign key indexes.

Right now indexes are automatically created for every
foreign key. Those indexes can not be dropped and
require some backhand magic to rename. As a result, if
the parent table is small and the child table large, the
foreign key index has very poor selectivity and can have
a terrible effect on performance.

1) The optimizer should be improved so that it doesn't
use really terrible indexes, right?

2) Does it make sense to maintain indexes that can never
be used?

3) Should InterBase just get out of the business of
creating indexes on foreign keys?

4) If InterBase creates indexes on foreign keys, should
it allow them to be dropped?

Cheers,

Ann