Thanks, Set. I have a much clearer picture now. At this early stage I
can still make changes to metadata to better suit tables and indexes
to what I plan to do in the future. I appreciate your help a lot.

-Benton


--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> No, not strange at all. I assumed there were no other index and that

the

> choice was between NATURAL and this index. There's no way an ascending
> index can be used with not equal or less than (at least not in Fb 1.5),
> so you cannot hope for anything more than the index being used for the
> CUSTOMER comparison. It is used when you use >0 because it thinks that
> will eliminate records that are less than 0, but I guess the vast
> majority of your invoices fulfil this criteria anyway so there's no
> point in using this index in this particular case, there's nothing
> quicker than using NATURAL if you cannot eliminate any records

(though a

> descending index if you were looking for those few invoices with
> INVOICETOTAL < 0 could be very useful). So, with your additional
> information about the foreign key, I'd say that the optimizers' choice
> is not strange at all and that it is the optimal plan for your

query. It

> cannot be further optimized in Firebird 1.5.
>
> As for lots of records affecting the plan, well, if there are only a
> handful of records (say you only put in records for customer 123 and

one

> or two more customers) the index selectivity would be lousy and the
> optimizer would be likely to prefer another index if available. With

one

> million customers the selectivity is likely to be considerably better
> and the likelyhood for the index to be chosen would be bigger.
>
> Set
>
> dr_bentonquest wrote:
> > Strange, isn't it? The index is picked up when using "greater than
> > zero", but it is NOT when using "different than zero" (<> symbol) or
> > "not equal to zero" (not field_name=0) or with your suggestion
> > (field_name+0 <> 0)
> >
> > I should add this: When using "<>0" and "not field_name=0" the proper
> > index is NOT picked up, but another index is. In this cases, the
> > optimizer chooses an index that contains only the first field,
> > CUSTOMER, because this field is a FK on the table and a RDB$FOREIGN
> > index exists for it. This is the index picked up by the optimizer. But
> > of course this is not optimal.
> >
> > I should also add that at this time the table has a few records only,
> > it's part of a new project with no user-entered data yet.
> >
> > I know it sounds strange, but could it be that the problem solves once
> > a lot of records are added to the table? I'd be surprised.
> >
> > Thanks,
> >
> > -Benton
>