Hi Colin,
you forgot to tell us one essential part - the chosen plan.

--- In firebird-support@yahoogroups.com, "Colin Fraser" wrote:
> I have been trying to speed up a query from 10's of minutes into
> something more usable.
>
> We have managed to do it by adding in an index on BigTable
> (described below) on the Status field, but there are only 8 or 9
> values of status, so I thought this was a no-no. To further
> explain...
>
> We have 2 tables, say BigTable and SmallTable. SmallTable has a
> couple of thousand rows, BigTable has millions of rows.
>
> SELECT
> BigTable.Id,
> BigTable.Type_Id,
> BigTable.Status,
> BigTable.OtherInfo,
> SmallTable.Code,
> SmallTable.Name
> FROM BigTable
> INNER JOIN SmallTable ON (BigTable.Type_ID = SmallTable.ID)
> WHERE (
> (BigTable.Status < 8)
> AND (SmallTable.NonIndexedField <> 0 )
> )
>
> Indexes we have are BigTable.Id, BigTable.Type_Id, SmallTable.Id
> (all primary/foreign keys), and we have a manual index on
> BigTable.Status, BigTable.Type_Id combined. I did this because I
> thought you were not supposed to have an index on BigTable.Status
> because it only had 8 or 9 different values.
>
> We changed (BigTable.Status < 8) to (BigTable.Status = 8) and it
> flies (it used the previously mentioned manual index). We then
> changed it to a series of OR statements (to mimic the < 8) and it
> crawls (no longer uses the manual index).
>
> BUT... If we have an index on Status itself, then it flies again,
> even with all the ORs... So, performance wise, this works, but I
> just wonder why it works so well when I thought you were not
> supposed do create such an index.
>
> So, should we keep the index??? The statistics on that index was
> 0.10000??somthing. I am not sure how to read those statistics, but
> it did stand out from the other indexes which had statistics a lot
> smaller.
>
> Why is it bad to create such an index when it seems to speed up the
> query so much?

Well, to create such an index would definitely slow down updates and
deletes, though having an index on BigTable(Status, ID) - note that I
used ID and not Type_ID - could be useful for this query if the vast
majority of records contained status >=8.

Unless this is the case, I would simply recommend you to drop any
index for the status field altogether, though if other queries depend
on it to find rare statuses, then you could tweak your query by
changing your where clause to (BigTable.Status < 8 or 2=0).

Though I am still qurious about your plans...

HTH,
Set