--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@...> wrote:

>
>
> My guess is, that Firebird will "materialize" the resultset for
> FROM TABLE WHERE FLAG='Y'
> before applying the FIRST 50 to it.
>

That is certainly what we are observing. It's too bad it is not smart
enought to realize there are no ordering clauses and quit when it has
the desired number of records.

>
> If "flag" indicates a few rows amongst lots of rows, why not try
> a different and more scalable solution.
>
> Have a table:
> FLAGGED_RECORDS
> ID, primary key
>
> that points to "TABLE"
>
> Whenever you flag a record, insert it into here.
>
> Now, getting the flagged records is a matter of:
>
> select first 50 *
> from table join flagged_records on (id = id)
>
> Given that "ID" would be the PK in your table, this should be faster if
> it's only a couple of records.
>

That is a fine solution if, as you mentioned, there are a small number
of records that satisfy the query. Unfortunately, for our situation,
the number of records with the flag set can vary from a few to several
million.