That might work, as might the equivalent any more normal way to do it:

select max(id) from table

But it may not be safe to assume that every ID is used. In Firebird,
IDs are often allocated by generators. If there is a problem with
inserting a record, such as a foreign key violation or check
constraint failure, the number is never used.

Also, because of the way firebird indexes work, you would need a
descending index on the ID column to efficiently run that query. Also
sometimes, a record will be deleted from the table.

Adam


--- In firebird-support@yahoogroups.com, "ibrahim bulut"
<i.bulut@i...> wrote:

> try this
>
> SELECT FIRST 1 ID FROM TABLE
> ORDER BY ID DESC
>
> best regards
>
>
> ----- Original Message -----
> From: "Lester Caine" <lester@l...>
> To: <firebird-support@yahoogroups.com>
> Sent: Monday, February 28, 2005 10:20 AM
> Subject: Re: [firebird-support] Performance of the count Function.
>
>
> >
> > williamvdw2004 wrote:
> >
> >> I have a table with about 630 000 records. I do the following count
> >> query. select count(transactionkey) from account.
> >>
> >> This query takes about 30 seconds the first time and 3 seconds
> >> subsequently. Transactionkey (bigint) is defined as the pimarykey of
> >> the table I have also added a descending index to the transactionkey
> >> column, page size is 8192. If I change the query to count the rows
> >> in the backup table the same thing 30 seconds the first time and 3
> >> seconds subsequently. Is there anything I can do to speed this up
> >> (the first query?)
> >
> > The problem is not so much the difference, but the fact that you are
> > trying to count at all. Once you have several users accessing the

table,

> > and adding/deleting records, then the cached count time will not

help,

> > since the count will have to visit all records again to see if

they are

> > now valid for YOUR transaction - giving the 30 second time again.
> >
> > If you actually need a valid count, then think about maintaining

it with

> > triggers and storing it in a separate 'count' table. That way you

only

> > need a single record read to access it.
> >
> > We have just had the same 'complaint' over on one of the PHP lists

from

> > someone who is used to MySQL giving an instant count. But that only
> > works for their old style simple tables, now that they are

starting to

> > handle transactions properly the same problems are arising there.

SO you

> > need to know why you are using COUNT, and if there is a more

appropriate

> > way of doing it in your situation ;)
> >
> > --
> > Lester Caine
> > -----------------------------
> > L.S.Caine Electronic Services
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >