Now you've got me curious: does that mean that Firebird transacts data,
but not the index? The way I understand the situation, is that you have
to make a trip to each row because the index may already contain data
from inserts which have been performed after the query's transaction has
been started or are still uncommitted. If this is the case: is it
possible to improve the performance by lowering the isolation level?

Tobias.

David Johnson wrote:

> 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]
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>