--- In firebird-support@yahoogroups.com, "russt4" <russt@a...> 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.
> 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". 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.
>

Hello Russ,

As Helen has pointed out, the reason the index wasn't used is because
using the index would not give any help. Also remember that each
transaction could possibly get a different value from such a query
depending on what has happenned in the time between the transactions
started, and what each transaction has done to now.

Of course you could have a separate table that has TableName and
RecordCount and using triggers increment or decrement it, BUT you will
have problems if two transactions try and work on the same table.

A really clever approach can be found here:

http://groups.yahoo.com/group/firebird-support/message/56457

This eliminates the problems with the first approach.


Hope that helps
Adam