MySQL doesn't support transaction level transparency. Because Firebird is is truly "multi-user", Firebird must physically address each of the rows being counted to ensure that it is actually there. If there are a large number of rows then this will take a longer time (10 ms per page read).

MySQL presumes (unsafely) that the row in the index is actually in the table.

----- Original Message -----
From: Martin Catherall
To: firebird-support@yahoogroups.com
Sent: Tuesday, April 06, 2004 10:02 PM
Subject: RE: [firebird-support] query performance problem


Hi,

I'm only guessing here but try

select count(ID), finishedStep from item where job=22 group by
finishedStep

instead of

select count(*), finishedStep from item where job=22 group by
finishedStep

on other database interating through an index is much faster that
getting a count of every column.

you could also try optimizing the query by placing an index on "job"

cheers

martin




Check out www.ubique.webscape.co.nz <http://www.ubique.webscape.co.nz/>
Ubique --> "Drives Your Business"

W E B S C A P E
PO Box 22571
25 Carlyle Street
Christchurch
http://www.webscape.co.nz <http://www.webscape.co.nz/>

Phone 03 964 4020
Fax 03 365 9115
Email mc@... <mailto:tmc@...>

-----Original Message-----
From: Tobias Jenkner [mailto:tjenkner@...]
Sent: Wednesday, 7 April 2004 4:30 a.m.
To: firebird-support@yahoogroups.com
Subject: [firebird-support] query performance problem


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);"

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


* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe
>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms
of Service <http://docs.yahoo.com/info/terms/> .



=========================================================
This e-mail has been scanned for Viruses and Content and cleared by NetIQ MailMarshal


[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]