MAX requires a DESCending index (default is ASCending), otherwise it
walks the whole table. Indexes can only go one way with IB. This
was discussed just this past week. If you want the whole story
type 'index' into the group page search box and have a look at the
recent posts.

You might also want to consider a composite index rather than
individual indexes in this case.

Regards,
Andrew Ferguson
-- I put the "I" in "team".

--- In ib-support@y..., Bob Murdoch <ram5@e...> wrote:
> I have a Calendar table, which contains all dates for a twenty year
time
> span. The table looks like this:
>
> create table Calendar (
> cal_date date,
> fiscal_week integer,
> fiscal_month integer,
> fiscal_season integer,
> fiscal_year integer)
>
> I have seperate indexes on cal_date, fiscal_season, and fiscal_year.
>
> I'm creating a view to select the last date of each week for a
season:
>
> create view fiscal_saturday as
> select
> fiscal_week, fiscal_month, fiscal_season, fiscal_year, max
(cal_date)
> from
> calendar
> group by
> fiscal_week, fiscal_month, fiscal_year
>
>
> If I run the following query against the view, no indexes are used:
> select * from fiscal_saturday where fiscal_season = 0 and
fiscal_year
> = 2002
>
>
> I'm assuming that the way the view works is that it has to build
it's
> result set first, then filter that to match the restrictions of the
where
> clause.
>
> My question is simply how can I speed this up? I actually need to
join
> this to another table for selecting some data, and the 15000 reads
against
> the calendar table are killing it's performance.
>
> tia,
>
> Bob M..