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

Hi Helen, Adam, thanks for your detailed replies. I assumed
that something like "select distinct "id" from mytable" could
use the information in the index on "id" because, as far as
I understand it, the index includes all the different values
of "id". If all the result information is already in the index
then it must be faster to extract it from the index than from
the entire table. I'm curious now which of my assumptions is
wrong. Is it not the case that the index contains all the
distinct values of "id", or is firebird not performing this
optimization?

The reason this has come up for me is that I'm migrating from
mysql to firebird (because I like the advanced features of
firebird!), and these are queries that previously ran in
acceptable time under mysql. I suppose that mysql does checks
for naive queries and in general holds the hand of user. Thanks
for pointing out the solution to the "select count(*)" problem.
I can already see an equivalent solution for the other queries.
But doesn't writing all these trigger functions seem more
complicated and error prone?

Cheers, -Russ