Hi Colin,
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

PLAN JOIN (SmallTable NATURAL,BigTable INDEX (FK2_BigTable,
BigTable_IDX1)

or

PLAN JOIN (BigTable INDEX (BigTable_IDX1) SmallTable (PK))

for the second query. With your current query and distribution of
Status, I'd guess both of these to be OK.

If this is correct, you've accidentally taught me something I never
thought of, and that is that for fields with lots of duplicates with
uneven distribution so that an index can be useful, then an index on
(<duplicate field>, <FK>) may cause problems that are more unlikely to
happen if using (<duplicate field>, <PK>).

HTH,
Set

--- In firebird-support@yahoogroups.com, "C Fraser" wrote:
> Sorry, the plans are as follows:
>
> For the statement below (which has the manual index added, and is
> the fastest by far)
>
> WHERE (
> (
> (BigTable.Status = 0)
> OR (BigTable.Status = 1)
> OR (BigTable.Status = 2)
> OR (BigTable.Status = 3)
> OR (BigTable.Status = 4)
> OR (BigTable.Status = 5)
> OR (BigTable.Status = 6)
> OR (BigTable.Status = 7)
> )
> AND
> (SmallTable.NonIndexedField <> 0)
> )
>
> PLAN JOIN (SmallTable NATURAL,BigTable INDEX
> (FK2_BigTable,BigTable_IDX2,BigTable_IDX2,BigTable_IDX2,
> BigTable_IDX2,BigTable_IDX2,BigTable_IDX2,BigTable_IDX2,
> BigTable_IDX2))
>
> Where FK2_BigTable is the index to the SmallTable and BigTable_IDX2
> is the index I created on the Status field (which I thought was bad
> because it only contains a few values).
>
> For the where statement
>
> WHERE (
> (BigTable.Status < 8)
> AND (SmallTable.NonIndexedField <> 0 )
>
> PLAN JOIN (SmallTable NATURAL,BigTable INDEX (BigTable_IDX1))
>
> Where BigTable_IDX1 is another index that I created which is on the
> Status and the BigTable.Type_Id field which is the foreign key to
> SmallTable. I thought this was the preferred way of adding an index
> on a column that contains lots of duplicate data.
>
> Hope this provides some more light on the subject.