Many thanks. its speeded up enormously. The sql select that i posted
was trimmed down for clarity. Thats why the index was specified a 2nd
time.
I dont understand, however, why the "or 2 = 0" inhibits the use of
the index.


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

> --- In firebird-support@yahoogroups.com, "colinriley666" wrote:
> > Why does this take so long? (FB 1.0)
> > 9 seconds with descending index IX_VI_CNKNUMMER, 6 seconds with
> > ascending.
> >
> > I remove the "and vi_cnknummer > '0000099' ", it takes 31ms
> >
> > regards, Colin
> >
> >
> > select vo_vsnummer, vi_isuitgesteld
> > from ftbvoorschriften
> > join ftbverkoopitems on vi_vsnummer = vo_nummer
> > where vo_vsnummer between 172900 and 172999
> > and vi_cnknummer > '0000099'
> >
> > Plan
> > PLAN JOIN (FTBVOORSCHRIFTEN INDEX

(IX_VO_VSNUMMER),FTBVERKOOPITEMS

> > INDEX (IX_VI_VSNUMMER,IX_VI_CNKNUMMER,IX_VI_CNKNUMMER))
>
> I assume there are lots of records with vi_cnknummer > '0000099',
> whereas vi_vsnummer does not contain too many duplicates. I.e. you

do

> not want to use the IX_VI_CNKNUMMER index something which can be
> prevented e.g. by (and vi_cnknummer > '0000099' or 2=0).
>
> By the way, I don't think you are telling us the entire truth,

since I

> cannot understand why the IX_VI_CNKNUMMER index is referenced twice

in

> the plan.
>
> Set