--- In firebird-support@yahoogroups.com, Matthias Hanft <mh@...> wrote:

>
> Svein Erling Tysvær wrote:
>
> > 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.
>
> You're right. I thought it would be easy for Firebird to get the
> MAX value by an ascending index, too ("just use the last index
> entry"), but maybe I have not understood the index concept fully
> yet :-)

With a unidirectional index there are no 'backwards' pointers.
Remember that indices are global to all transactions, so they will
contain uncommitted inserts, deleted records etc. It is quite possible
that the last item in the index is of no interest to you. In that
case, you would have to read the whole index up to N-1 again to try
the next one. Worst case you have N/2*(N+1) reads of the index, which
would be much worse than a simple natural scan.

Many databases have bidirectional indices which would be able to solve
this particular problem. Of course the downside of this is that their
indices are less dense, there are always tradeoffs.

> > 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.
>
> Done. Here are the results:
>
> select min(datetime) --->>> uses ascending index
> select max(datetime) --->>> uses descending index
> select min(datetime), max(datetime) --->>> uses natural?!?!?!
>
> I cannot believe that it is faster to read all rows than just
> using two indexes, one after another?!

I agree with you there, and providing you did the experiment correctly
it would point to an optimiser limitation.

In this case I would use a stored procedure to run the queries separately.

eg

CREATE PROCEDURE TEST
(
)
RETURNS
(
MINVALUE TIMESTAMP
MAXVALUE TIMESTAMP
)
AS
BEGIN
select min(datetime)
from ....
into :MINVALUE;

select max(datetime)
from ....
into :MAXVALUE;

suspend;
END
^

The optimiser will have no trouble providing both indices are available.

As Set mentioned, count(*) causes a table scan. If there is a where
condition, then an index may be used to narrow it down, but every
record that matches the criteria will need to be visited. This is
because the index contains record versions that your transaction can
not see. These record versions must not be included in your count.

Adam