On Sep 15, 2005, at 8:43 AM, D. Richard Hipp wrote:
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.
Interesting. But, with the above suggestion, every INSERT or DELETE
would slow down anyway as much as it would have were SQLite to maintain
meta information itself, no?
If the table were small enough, the overhead because of the above
maintenance would not be "visible," and if the table were large enough,
then the above overhead would be necessary either via SQLite doing it
automatically or the user doing it via the suggested TRIGGER method.
Hence, it might be worthwhile maintaining the meta information no
matter what... most of the folks won't ever notice it, and everyone
would marvel at how quickly COUNT(*) was returning the results.
--
Puneet Kishor
