--- In firebird-support@yahoogroups.com, Stefan Heymann <lists@...> wrote:

>
> What is the best and least costly way to check for the existence of at
> least one record in the result set of a SELECT?
>
> I just want to know if or if not a query will return anything. I don't
> care about the contents. My query involves a NATURAL scan and I want
> to stop the search engine as early as possible.
>
> Is a SELECT FIRST 1 the solution? Or is there something else which is
> better?

Well I don't know if there is anything better (apart from exists). If
your record is particularly wide (a bunch of long varchars), then you
could do better.

One thing to be very careful of is sorting. If you use an order by
clause, or an operation with an implicit implementation order by
(group by, distinct, union), then you will need an index on the
ordered field otherwise the whole execution time must be absorbed
before returning the first 1 (which is bad).

But

select first 1 1
from Mytable
where Somecondition='T'

Is about as quick as a natural read can be. Natural reads and first n
is unpredictable, you may be lucky and get it with the first read, or
it might not be until the final record that you get a match, and you
need to read the entire table to answer that there are no records.

Adam