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

Adapted Plan
PLAN JOIN (FTBVOORSCHRIFTEN INDEX (IX_VO_VSNUMMER),FTBVERKOOPITEMS
INDEX (IX_VI_VSNUMMER,IX_VI_CNKNUMMER,IX_VI_CNKNUMMER))



Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 9.312,00 ms
Avg fetch time: 49,01 ms

Memory
------------------------------------------------
Current: 38.972.173
Max : 39.303.148
Buffers: 8.192

Operations
------------------------------------------------
Read : 7
Writes : 0
Fetches: 53.985


Enchanced Info:
+--------------------------+-----------+-----------+---------+--------
-+---------+
| Table Name | Index | Non-Index | Updates |
Deletes | Inserts |
| | reads | reads |
| | |
+--------------------------+-----------+-----------+---------+--------
-+---------+
| FTBVERKOOPITEMS| 190 | 0 | 0 |
0 | 0 |
| FTBVOORSCHRIFTEN| 100 | 0 | 0 |
0 | 0 |
+--------------------------+-----------+-----------+---------+--------
-+---------+