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.
