Re: [sqlite] what's the fastest way to get the record count of a table?

[drh]

<[EMAIL PROTECTED]> wrote:
> Hi,
> I want to check the record count of a table every 5 seconds.
> It seems there's only one way to get a table's record count:
> select coun(*) from ATable
> 
> but if the table gets big(1,000,000 rows), it will get slow.
> Any good idea?

Create a separate table that has a single row and single
column for storing the record count:

    CREATE TABLE reccount(cnt INTEGER);

Then create triggers that fire on every insert or delete
and update the record count table.

    CREATE TRIGGER rc1 AFTER INSERT ON tableA BEGIN
      UPDATE reccount SET cnt=cnt+1;
    END;
    CREATE TRIGGER rc2 AFTER DELETE ON tableA BEGIN
      UPDATE reccount SET cnt=cnt-1;
    END;

Then to get the record count do:

   SELECT cnt FROM reccount;

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------