Hi Helen,

Thanks for the reply. Turns out that I missed that min(id) was using the
index. Now I have added the desc index on Id, the max(id) is using the
index too, which is cool.

Is there a way to get count(id) to use the primary key / an index? What I
am trying to do here is actually get the count of records very fast - I
have up to a million records in there. So I am subtracting the max and min
id (I know that there will be no gaps) whereby I can use the indexes. That
was easy in SAP which we are migrating from, but in Firebird it seems that
to make both use the index and do it in one statement you have to do:

select max(id) - (select min(id) from metrics) from metrics;

which is a little ugly to say the least! (Even doing it this way was a
last resort with SAP as count() took **ages**, so I would obviously prefer
to use the count(id) if I can get it quick enough).

Would you also mind explaining what you meant by "But something is not
right with the design if you are
storing meaningful data in your PK"? Here the PK is just an Id field. I
also have a table that contains just two foreign keys (id's from two
different tables) and I have made the PK the combination of the both. Does
that indicate something wrong?!

Thanks for your help,

David





Helen Borrie <helebor@...> on 09/10/2003 07:45:04 PM

Please respond to firebird-support@yahoogroups.com

To: firebird-support@yahoogroups.com
cc:
Subject: Re: [firebird-support] Indexing for Min and Max


At 02:35 PM 9/10/2003 -0400, you wrote:

>Hi,
>
>Nope, didn't know that. But I am using a primary key - is an index
>required too?

The PK already provides the ascending index. You have to create the desc
index yourself. But something is not right with the design if you are
storing meaningful data in your PK.

heLen






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



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/