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)