--- In firebird-support@yahoogroups.com, "Arno Brinkman" wrote:

> Hi,
>
> > here's some advice that I think will make you happy.
> >
> > Drop BigTable_IDX2, and change BigTable_IDX1 to be on the fields
> > (Status, ID) rather than (Status, Type_ID). Then you will get a
> > plan like
>
> I would prefer using (ID, Status) or (Type_ID, Status) and indeed
> drop single index on status (or is it FK ?).
> If you put status as FIRST field in the compound index the current
> engine could choose this one also for other queries where it may be
> bad. When using the status as second field in the index (i assume ID
> and Type_ID don't have so much duplicates) the engine cannot use
> this index unless TypeID or ID is also available. Note! that FB1
> still could be confused with these indices.

ID is Colins primary key. Surely an index on (<PK>, <DuplicateField>)
is never desireable? My suggestion was due to there being a very
uneven distribution of the Status field, with several millions of
records in the table of which maximum 100000 would have Status <> 8 or
9. Having the index (Type_ID, Status) does sound a bit better, if this
does not confuse the optimiser due to Type_ID being a foreign key with
a couple of thousand records in the small table that he is linking to.

Set