Hi Navin,
Excuse me if some of the points below repeat things you already know.
1. Dr. Hipp's advice not to create redundant indexes was *not* intended to
give you very quick row counts -- Simon Slavin et al had already given
advice to speed up row counts -- and just now Stefen Keller even provided
trigger sql statements for you. The trigger solution will result in
almost instantaneous row counts (at the expense of slightly slower inserts
and deletes.)
2. Alternatively, since you write that you only do deletes every 5 days,
then if by chance the time for running VACUUM just after these periodic
batch deletes is tolerable, then
SELECT max(ROWID) FROM hp_table1;
should give you instant satisfaction without using the TRIGGERs. (if you
choose this solution you would NOT specify WITHOUT ROWID of course.)
3. We're all assuming that your application truly NEEDS to know the row
count (and that you're not just using "select count(*) from hp_table1" as
some "arbitrary test sql" for sqlite.)
4. The fact that
select count(*) from very_large_table;
can be slow is *not* evidence that sqlite can't handle tables as large as
yours. Sqlite can update its b-trees till the cows come home. It was a
design trade-off. Sqlite could have been designed to always keep up with
table sizes, but inserts and deletes would be slightly slower for all
tables for all users, and in a great many applications it's not needed. On
the other hand, applications that *do* need quick access to the current row
count can have it using triggers. Other databases might have been
designed differently. (There *are* however, many applications ill-suited
to sqlite -- often because of high concurrency needs or the need for fancy
features, but row counting is not a veto item.
https://www.sqlite.org/whentouse.html)
5. IMPORTANTLY -- I see you tried different page sizes, but did you did you
also set a large CACHE size? E.g., "PRAGMA CACHE_SIZE= -1000000"
would set the cache to about 1 GByte. Did I see that your postgres test
was using 4GB? You'd want to compare using about the same cache I would
think. (A large cache alone does NOT substitute for one of the "fast
row count" solutions above, but it might make a big difference in your
other operations.)
https://www.sqlite.org/pragma.html#pragma_cache_size
Regards,
Donald Griggs
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
