At 09:43 AM 13-08-01 +0200, you wrote:

>Helen,
>
> > This occurs when you define your own index for a column that is also a primary
> > or foreign key. IB doesn't stop you doing this but it results in NO index being
> > used by the optimizer.
>
>the optimizer is not THAT bad; let's say that it can be fooled sometimes
>if duplicate indices exist. In my experience, it will more often pick up
>one of the two than none.

This is not my observation. The usual result of duplicate indices is that the optimizer's plan will show SORT(NATURAL..) when you expect it to use your index. As to "more often", this is completely the opposite to what I see. "Almost never" is closer to the real situation.

>Not that I am *by any means* suggesting to define duplicate indices! ;-)

IMO, this is one of *the* most important places to look when queries are slow. That and, of course, that old warhorse, indices of low selectivity auto-created for you from applying FOREIGN constraints to control/lookup values. ;-)

But some people HATE fast queries so it's all a matter of taste, really. :-))

rgds,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________