At 12:06 PM 10/10/2003 -0400, you wrote:

>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.

It's meaningful if you are depending on its value for something. In this
case, if you are making some decision based on its value (absolute or
relative) then it is meaningful. Worse if the value is being set by some
external means.

This is a very unreliable way to get a row count. If there's some reason
you must know the row count then Artur's suggestion is the nearest you will
come to a reliable answer. If you can do without the rowcount in a
non-file based, transactional RDBMS, then so much the better. It can never
be precise beyond the limits of the transaction that acquires it.

> 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?!

Not necessarily. I assume it's an intersection table for a many:many
relationship. As long as the two parent keys are not meaningful in any
way, and you have them properly covered with RI constraints, then it will
be OK.