Hi,

> We are trying to query a very large table (millions of records) and
> only retrieve a few rows using "FIRST n". From our tests, "SELECT
> FIRST 50 * FROM TABLE WHERE FLAG='Y'" doesn't run significantly faster
> than "SELECT * FROM TABLE WHERE FLAG='Y'". The FLAG field is combined
> in a compound index with a unique record number to avoid low selectivity.
>
> A review of previous postings appears to be contradictory. Several
> newsgroup postings indicate "FIRST n" doesn't make sense without an
> ordering clause. One newsgroup posting,
> news://news.atkin.com:119/43F0AC95.6090402@..., stated "If
> you don't include an order by, then the first x records are returned
> and the query stops" which is the behavior we would like but have not
> observed.
>
> We are not using an ordering clause since we do not care what order
> the records are in. We are trying to locate records that have been
> queued up for processing. Does anyone know of a way to retrieve a few
> records without the query going through the entire table?

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

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.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com