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