Subject Re: [firebird-support] select fieldA >= xxx is very very slow on Big table ?
Author Ann W. Harrison
On 12/19/2010 3:08 PM, Vander Clock Stephane wrote:

> Select
> First 1 skip 0 ID
> from
> where
> FIELDA>= xxx
> is very slow on big table (> 20 000 000 rows) ... it's take more than 10
> seconds to return :(
> this is an index on FIELDA

Stick in an ORDER BY FIELDA - I think it will help. As I have explained
several times recently - on this list at least once I think - Firebird
has two index access strategies, two-phase and navigational. In the
two-phase access mode, it first finds all matches in the index, setting
bits in record number bitmap, then finds records from the bitmap. In
navigational access it finds the first match in the index, returns that
record, then finds the next match, returns the record and so on until

Firebird normally uses two-phase access unless there's an ORDER BY
that exactly matches a single index. A FIRST will further encourage
navigational access. So the time is going to reading an index and
setting bits you don't care about.