| Subject | Re: [firebird-support] select fieldA >= xxx is very very slow on Big table ? |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2010-12-20T15:47:46Z |
On 12/19/2010 3:08 PM, Vander Clock Stephane wrote:
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
done.
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.
Cheers,
Ann
>Stick in an ORDER BY FIELDA - I think it will help. As I have explained
> Select
> First 1 skip 0 ID
> from
> TABLEA
> where
> FIELDA>= xxx
> PLAN (TABLEA INDEX (FIELDA_IDX))
>
> 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
>
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
done.
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.
Cheers,
Ann