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