Jeff Dunlap wrote:

>
> I plan to display some data ordered by a timestamp field and plan to allow
> the user to order either in ascending or descending order.

That's not a problem. You don't need any index at all to display
fields in a specific order. You need an ORDER BY clause in your
select statement. The primary reason for having indexes is to
locate a specific subset of your table.

However, to get good results, you have to ask very specific questions.
For example, to find the most expensive item sold today, you could
use:

select customer, item_name from first 1 items
where date_sold = CURRENT_DATE
order by price descending
or

select customer, item_name from first 1 items
order by date_sold, price descending

In the first case, if you have an index on date_sold - ascending or
descending, you should get pretty good performance. Firebird identifies
the items sold today, sorts them by descending price, and returns the
result.

In the second case, if you don't have a descending index on date_sold
and price, Firebird retrieves all the rows and sorts them by date_sold
and price. If you've been in business for a while, that's a lot of
rows (one hopes) and slow. If you do have such an index, then Firebird
gets the rows in index order and stops after the first. The weakness of
that solution is that you'll shortly find that you need lots of indexes.

The recommended solution is to retrieve as specific a subset of the
table as you can by indexed values, then let Firebird sort them.

Regards,

Ann