Can you post the DDL for the table, along with all index and constraint
DDL.
Also post index statistics, these can be obtained by the isql command
SHOW INDEX or more specifically SHOW INDEX MyTable
Please post plans for any DML you reference so we can see what your
optimizer is trying to do.

STORE_NO should have a foreign key pointing to a store table, but if you
have few stores then with FB 1.5 you'd hit a problem with large numbers
of duplicate keys.
With FB 2.0, the GROUP BY would then use the FK correctly and it should
be quick.
With no FK, it will have to scan all 2.1 million records, as the only
index is on ID,STORE_NO and partial keys can only be used from left to
right (if the key had been STORE_NO,ID then STORE_NO could be used, or
if ID had also been specified in the GROUP BY).

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of buppcpp
Sent: Tuesday, May 31, 2005 5:06 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Firebird 2.0 Indexing

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)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links