Greetings to all. I'm happy to see that the discussion took a better route
than I imagined, my comments below:

> -----Original Message-----
> From: Ann Harrison [mailto:harrison@...]
>
> 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.

And unfortunately, there's no out-of-standard (proprietary extension) way
to give automatic indexes a meaningful or custom name. I can give the
constraint itself a name, but not to the underlying index. (Same for PKs and
UKs but this might be another separated topic.)

> 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.

I assume one can translate that in newbie terms as "it's faster to do a
natural scan".

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

Although optimizers can be backed by strong mathematic models, I don't
think they are easy to build nor easier to make them aware of all important
facts in way that it doesn't take more time to run the optimizer than the
query itself. In other words, I'm not sure I must demand more and more
improvements to the optimizer.
I thought the optimizer did something with the selectivity. Right or wrong?
If it uses that information, it can take better decisions... but if the
selectivity is totally outdated... disastrous performance. Also, the only
ways to update selectivity seems to be SET STATISTICS INDEX and
backup/restore. Activating/deactivating indexes is another way, but this is
forbidden in the current design for PK/UK/FK.

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

I'm not sure what was your idea here, Ann. It resembles the discussion
whether IB should monitor data usage and guess the indexes that make
improvements. The plain answer to your question is NO, it doesn't make
sense. However, how would the engine come to that conclusion? By checking
that the referenced table has only a few records?

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

I must admit I'm fairly used to this feature. At least in ER/Studio I was
able to tailor the generation options for the script so only explicit
indexes (defined by me) should be put in the script. I don't remember if I
was able to do the same with Erwin and S-Designor. At the same time, I must
admit, too that one of the problems I fix very often when IB newcomers don't
read enough the manuals is they end up with duplicated indexes for PK/UK/FK.
Probably the feature may be downgraded to optional and controlled in the
configuration file but with factory default=OFF. Alternatively, an
"extension" might be added to these DDL declarations to indicate them
"create the index for me" but the normal FK declarations would be treated as
in almost any other engine: no magical indexes.

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

Or at least, activated/deactivated. This won't have necessarily the same
effect.

> Cheers,
>
> Ann

Thanks for taking my original post to a level more interesting for a
discussion.

C.
---------
Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente
http://members.xoom.com/cvalde