Svein Erling Tysvær wrote:

> 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

Hmmm... if the "IBExpert" plan analyzer doesn't lie to me, this is
not completely true:

select min(datetime), count(*) from requests
where datetime<'31.12.2999';

does use the (ascending) index, whereas

select min(datetime), max(datetime) from requests;

does not use any index at all, although there is no "count".

Beside this weird effect, one can apparently always make
Firebird use an index together with count(*) (at least,
if you have a primary key): Just replace

select count(*) from requests; /* natural */

by

select count(*) from requests
where id>0; /* primary key index */

At least, the Plan Analyzer tells me so. But with 10,000 rows,
there is not yet an execution speed difference. Maybe Firebird
does always the same, internally?

Matthias