afaik, sqlite doesn't store row counts so count(*) causes a full table scan.
On 12/23/05, Axel Mammes (gmail) <[EMAIL PROTECTED]> wrote: > Wouldn´t a SELECT COUNT(*) just read the table header and get the amount of > records from there? That should be faster and simpler than maintaning a > separate table for the counters. > > -----Original Message----- > From: Paul Bohme [mailto:[EMAIL PROTECTED] > Sent: Viernes, 23 de Diciembre de 2005 02:52 p.m. > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] ring buffer table > > Julien LEFORT wrote: > > >Hi, > >I would like to implement a log table with a finite dimension, for exemple > a > >table with 500 records, and when the last record is set in the table I > would > >like to come back at the first tuplet and write over the previous value > >recorded. I think it's the way SQLite journal is implmented. > >Is there any way to declare this table with these properties included, so I > > >don't have to add code to do this function? > >Thanks > > > > > > I need something similar, so was planning a couple of simple tricks to > keep the overhead low. I want to avoid "select count" queries as much > as possible, so how does the following sound: > > - insert/delete triggers on the table I want to limit the size of > - a separate table with a row that contains a single counter, that is > incremented and decremented by the above triggers > - a process that runs at intervals that checks the counter, and if over > the limit trims the appropriate number of records > > Seems like a reasonable way to keep a table to a limited growth without > too much of a hit on every insert. The counter table would be a serious > hot spot in other databases, but SQLite's locking is simple enough that > it doesn't seem like it will be a problem. > > -P > > -- Cory Nelson http://www.int64.org
