Subject Re: MAX vs. FIRST 1
Author Adam
> I always thought MAX is faster because
> I would have expected the database to simply consult the index and
come out
> with the largest value, as opposed to first selecting all matching
> then sorting them and finally returning the first one. (Assuming I
have an
> applicable index, obviously)

It depends on the plan.

There are two ways to sort records when you have an index handy.
Either use the index to read them off disk in the order they need to
be (indexed walk), or to read them off disk in the order they are
stored, and sort them in memory (natural read then sort).

The former is much faster to get the first records, but in general
slower to complete. From my experience, Firebird tends to prefer the
indexed walk if you place a 'first n' condition into the query,
although I don't think I have ever needed to do it with a descending
index and query.

I would expect the 'max' and 'first 1 ... order by ... desc' to be