At 12:46 AM 5/11/2004 +0000, you 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,

This one is OK to index on its own, provided plenty of ids are distributed
through the table.

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

Don't use a single-column index on this column and don't make this column a
foreign key. (!!!!) Make a composite index consisting of status + primary
key, *in that order*.

I'm not sure why your Subject for this question is "aggregate function
optimization". The optimization here applies to any search, including, but
not limited to, selecting the rows for an aggregation.

./hb