At 08:30 2004.04.06, you wrote:

>Hi everybody,
>
>i got a firebird 1.5 db running on windows xp professional. in this
>database i have a table that looks like this:
>
>"CREATE TABLE ITEM (
> ID NUMERIC (18, 0) NOT NULL,
> INDEXCLASS NUMERIC (18, 0),
> FINISHEDSTEP INTEGER,
> JOB INTEGER,
> ARCHIVE_ITEM_ID VARCHAR (255) CHARACTER SET NONE COLLATE NONE);
>
>ALTER TABLE ITEM ADD PRIMARY KEY (ID);
>
>ALTER TABLE ITEM ADD CONSTRAINT FK317B1319BBD FOREIGN KEY (JOB)
>REFERENCES JOB (ID);
>ALTER TABLE ITEM ADD CONSTRAINT FK317B132AFA43C6 FOREIGN KEY
>(INDEXCLASS) REFERENCES INDEX_CLASS (ID);
>ALTER TABLE ITEM ADD CONSTRAINT FK317B13C3E6B25E FOREIGN KEY
>(FINISHEDSTEP) REFERENCES STEP (ID);
>
>CREATE INDEX FK317B1319BBD ON ITEM (JOB);
>CREATE INDEX FK317B132AFA43C6 ON ITEM (INDEXCLASS);
>CREATE INDEX FK317B13C3E6B25E ON ITEM (FINISHEDSTEP);
>CREATE INDEX FOO ON ITEM (JOB, FINISHEDSTEP);"

If I remembered correctly, create constraint would create related index.
So, the create index statements might not necessary. Is this assumption
correct ? Somebody please correct me. Is that index the same field would
result in a degrade in performance ?

>i execute the following query on this table:
>"select count(*), finishedStep from item where job=22 group by finishedStep"
>
>with about 200000 entries in the table it takes about 2 seconds to
>execute. mysql in comparison only needs a few dozen milliseconds.
>
>does anybody know why this query is so slow on firebird and why there is
>such a huge difference to mysql?
>
>thanks in advance,
>
>Tobias Jenkner.
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>