On Thu, 2005-09-15 at 13:59 +0100, Da Martian wrote:
> Hi
> I have 3 million rows in a table which takes up about 3.1GB on disk. The 
> count(*) is slow. 
> I have run the analyze, but apart from creating the stats table it does 
> nothing. 
> Any reason why this is? Can it be improved ?

SQLite always does a full table scan for count(*).  It
does not keep meta information on tables to speed this
process up.

Not keeping meta information is a deliberate design
decision.  If each table stored a count (or better, each
node of the btree stored a count) then much more updating
would have to occur on every INSERT or DELETE.  This
would slow down INSERT and DELETE, even in the common
case where count(*) speed is unimportant.

If you really need a fast COUNT, then you can create
a trigger on INSERT and DELETE that updates a running
count in a separate table then query that separate
table to find the latest count.

Reply via email to