You can put on a multiple index ie, index on "id,status". Its a bit of
a trade-off, performance vs file size. I recommend you download
ibplanalyser (url escapes me sorry), and run your quwey against
different index setups and choose the one that most suits the data.

--- In firebird-support@yahoogroups.com, "james_027" <james_027@t...>
wrote:

>
> Hi,
>
> I will be using a lot of statements like this ...
>
> select sum(qty)
> from table
> where id = :id and status = :status
>
> in a table with tens of thousand record and eventually it will grow to
> hundreds of thousand record.
>
> What are the things I have to know/do to achieve maximum performace
> ... the id field is a integer field where in the same value can be
> enormous in the table or just a few, while the status is a char(3)
> field where the possible value can be only 10, where in one of the
> value is dominating, like around 95% of the records have this same
> value and the rest share the other 9 possible value.
>
> So... my question is which field should be index or not... and are
> there other concerns I have to notice?
>
> Please advice. Thanks.
>
> Regards,
> James