On Dec 25, 2011, at 11:21 PM, Vander Clock Stephane <svanderclock@...> wrote:

> not sure i understand :)
>

OK. You could check for discussions of navigational indexes vs two-stage (or two-phase) index look-ups. Basically, Firebird normally returns rows from an index in storage order. In that mode it can combine several indexes. If it must return rows in index order, it has to walk that index, returning rows one by one (aka navigational access). That eliminates the ability to combine indexes. The combination of STARTING and ORDER suggests that the query will return a small subset of the total result set, so Firebird defaults to using navigational access.


I wonder, vaguely, which is the way I normally wonder, whether there might be another way to use indexes to verify that a particular record has a particular value. Currently, if I remember right, looking for all properties with three or four bedrooms builds a sparse array of all record numbers that have 3 or 4 bedrooms. But that index is ordered by number of bedrooms and record number. So it might be possible to check that a specific record number has a specific number of bedooms without generating an enormous sparse bit array.


Cheers,

Ann