Subject Re: FB 1.5 final hangs with this exists statement
Author Svein Erling
--- In, James wrote:
> My question is putting an index in custid makes a lot of difference
> in the performance while putting an index in grandtotal dosn't
> affect after all. I don't get it why since the Grand Total field
> is also involve in the where clause...

Well, there are at least two reasons for the custid being the most
important field to index:

1) custid is referenced in the where clause of both the main select
and the subselect (making it possible to use the index in both parts
of the plan), whereas grandtotal is referenced only in the subselect.

2) I assume fewer records have custid = 205 than grandtotal > <some

Also, Arno told me at last years conference in Fulda something about
descending indexes only being advantageous for max and generally not
very useful for >. I have no idea why and do not know whether or not
he has discovered something more in "his" optimizer making better use
of descending indexes.