When I group by the primary key, GROUP BY do not use the index.

And the query takes an extremely long time!

I really think there are some problems with GROUP BY and DISTINCT
guys.




--- In firebird-support@yahoogroups.com, "buppcpp" <buppcpp@y...>
wrote:

> GROUP BY are even worst than distincts.
>
> ex. SELECT store_no FROM mytable GROUP BY store_no;
>
> This takes ~4 mins!!
>
> mytables has ~2.1 millions records.
>
> Table design is:
>
> ID VARCHAR(16) (PK)
> STORE_NO INTEGER (PK)
> .
> .
> .
>
>
> I also have an index on STORE_NO only.
>
> My current system only takes 5 secs to perform this same query.
>
> DISTINCT and GROUP BY must be looked at, it's killing this

database!

>
> Thanks
>
>
>
> --- In firebird-support@yahoogroups.com, Daniel Rail <daniel@a...>
> wrote:
> > Hi,
> >
> > At May 29, 2005, 08:18, buppcpp wrote:
> >
> > > Were the changes to the indexes in 2.0 suppose to provide more
> than
> > > larger indexes?
> >
> > I think there is. And, there has been some changes in the
> optimizer
> > as well.
> >
> > > The reason I'm asking is because some of my queries still run
> just
> > > as slow in 2.0 as they did 1.5.2.
> >
> > Now, it's the time to give the feedback while FB 2.0 is still in
> > development.
> >
> > > Will these types of indexing issues be taken care of 2.0, if
> not,
> > > when?
> >
> > FB 2 is still in alpha, and all reporting related to it should

be

> done
> > in the Firebird-devel group on Sourceforge, until it is

officially

> > released.
> >
> > > I for one would prefer to have the indexing system fixed in

2.0,

> > > because I think we may need to sit on it for awhile, while 3.0
> gets
> > > all of it's bugs knocked out. Plus, 3.0 will have the same
> > > problems, if it's not taken care of now, but with the overhead
> of
> > > SMP code (for 1 CPU systems).
> >
> > I don't think there will be much overhead, if any, for SMP

support

> in
> > Superserver. Since the changes in Vulcan are that the thread
> > scheduling will be managed by the OS and not by FB.
> >
> > --
> > Best regards,
> > Daniel Rail
> > Senior Software Developer
> > ACCRA Consultants Inc. (www.accra.ca)
> > ACCRA Med Software Inc. (www.filopto.com)