C Fraser wrote:

>I have been trying to speed up a query from 10's of minutes into
>something more usable.
>
>
>

This is not a support list, it's to discuss general issues about
firebird. For support question, please go to
firebird-support@yahoogroups.com.

>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.
>
>
>

The problem with this index is the big amount of duplicate values: Try
once to delete a record from BitTable - you will realise that with
having this index it will be much slower - you can observe similar
effect with backup/restore.
The solution for your problem is often to make a combined index like

create index xxx on BigTable(Status, ID)
or maybe even
create index xxxx on BigTable(Type_ID, Status)

just look for a combination of fields which might be usefull in your
selects.

By the way: you can have similiar problems with your foreign key index
on BigTable.Type_ID which will also have a lot of duplicates. That's why
I'm most often using trigger for referential integrity and not foreign
keys as foreign keys will automatically create foreign indexes.

cheers
Daniel