Helen,

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

funny how you seem to be sure that what you see is the "real situation"
and anything else is not. ;-)

I connected to the employee.gdb sample. The employee table has a PK on
emp_no with a system defined index RDB$PRIMARY7.

select
from employee
order by emp_no

the plan is ORDER RDB$PRIMARY7.

I added an additional index of emp_no, and repeated the query (yes I
have flushed the cache); the plan was exactly the same. I tried with an
additional UNIQUE index, same results.

I then tried to add a unique index called FOO on (emp_no, first_name),
and the plan was ORDER FOO. For some reason IB has decided that the
second index was more wuited for the ordering task (why, it escapes me).

So, I have not been able to have IB (5.6) *not* use an index to serve my
ORDER BY.
This is what *I* see (it must be my proverbial luck in action again!
;-)).

I have observed, in the past, cases in which the optimizer did not
select an index, but they were far more complex queries, and in those
cases I was unsure that the optimizer's choice was wrong,
performance-wise, anyway. That's why I said that "more often" it does
the right thing in my experience.

WRT to the SORT clause, I believe it is used only when IB sorts the
result set after collection the records (as opposite to the ORDER
clause), and which case the index optionally used to retrieve them has
not much importance.

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

I agree completely for the selectivity thing, a bit less for duplicate
indices. I'll repeat it again just to be safe: I do agree they are
useless; I don't think they are that armful for selects (they could be
for inserts and updates, though).

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

How could I charge my apps by the minute if the queries were too fast?
;-)
Ciao
--
____
_/\/ando