try this

SELECT FIRST 1 ID FROM TABLE
ORDER BY ID DESC

best regards

----- Original Message -----
From: "Lester Caine" <lester@...>
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
>
>
>
>
>
>
>
>