Sorry Matthias, I answered too quickly and didn't even notice you had
'count(*)' as part of your query. With count(*) Firebird has to check
every record anyway, and the only way is to do a natural scan. No
index will be of any help. count(*) is always slow without a where
clause, though a quicker way to do something similar can be found
here: http://www.fbtalk.net/viewtopic.php?id=164

Sorry for not reading your question completely the first time,
Set

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær wrote:
>
> 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