At 04:54 AM 25/04/2005 +0000, you wrote:

>Hello, I'm experiencing very slow performance on quite simple
>queries. For example, a query like:
> select count(*) from mytable
>takes about five minutes on a 700MB database with 150k rows.

Firebird doesn't store record counts of tables so this is a veeerrry slow
operation: the count literally starts at the first record and walks its
way through the whole table. For what sort of task will you need a record
count of the table?

>
>And a query like:
> select distinct "id" from mytable
>or:
> select "id" from mytable group by "id"
>also takes around five minutes, even though it says it is using
>the index created on "id".

The first query can't use an index. In the second, to determine
"distinctness", without any limiting WHERE clause, it's necessary to read
the whole table.

>It appears that firebird is
>searching over every row in the table, and isn't just using
>the information in the index to construct the result.

In the first two cases, it does have to read every row. How would you
suppose an index could be used ?

In the third case, using the index of a (presumably unique) value isn't
going to win anything. The optimizer will make a heuristic choice not to
add overhead by referring to an index when it can get precisely the same
result without it. What use are you going to make of a query like this on
150k rows, when select "id" from mytable, without the group by, achieves
the same result in a fraction of the time?

>Obviously, I don't seem to understand how the firebird
>optimizer works. Can anyone point me in the right direction?

Seach the ibphoenix website ("website search") with the term
"+optimizer". There are a few articles there that will help you to
understand what goes on when the optimizer prepares a query.

As a general rule, though, performing any operation on 150k rows without
any limiting clause is going to be - uh - slow. Give the optimizer
something to search on and it will use suitable indexes if they are
available/appropriate.

./hb