Why bother using an index if you have to go natural on a table anyway?

You're right in that min(datetime) may use your index, but since
indexes are unidirectional, you have no index to use with
max(datetime). Having to look through every record to find the
maximum, Firebird simply doesn't bother to use the index to find the
minimum.

Try adding another index, a DESCending index on your timestamp column.
Then I'd expect Firebird to use both indexes. Though don't take my
word for it (I haven't tried) - test it yourself.

By the way, your 'where datetime <= '31.12.2999'' is the wrong
solution (unless the majority of your dates are in the next
millenium). If you have to traverse all records, then NATURAL is far
quicker than using an index. Your way of thought is OK, but the
solution should be creating the DESCending index.

HTH,
Set

--- In firebird-support@yahoogroups.com, Matthias Hanft wrote:
> Hello,
>
> I have a table called "requests" with a TIMESTAMP column
> called "datetime". Beside the primary key on column ID,
> there is an index on the "datetime" column to speed up
> searches like "select * from requests where datetime
> between ? and ?". This works without any problems.
>
> Now I'd like to get the first and the last timestamp, and
> the number of total entries. I tried
>
> select min(datetime), max(datetime), count(*) from requests;
>
> which does work, but I recognized that Firebird uses a
> "natural" plan with non-indexed reads only.
>
> If I include
>
> where datetime <= '31.12.2999';
>
> in the select statement, the datetime index is used, and
> there are indexed reads only.
>
> I wonder why a "where" clause causes Firebird to use the
> index, whereas just "min" does obviously not - although
> for a naive human being, just "min" should be predestinated
> for using an index - isn't it?
>
> I can live with that (and, on December 31st, 2999, I'll
> be dead anyway :-)), but I'd be glad if anyone could
> explain the reason for this.
>
> Best regards
>
> Matthias