Hi All,
I'm trying to efficiently display a large table to users. I have a
test database with 2.7M records that is 1.6 GB in size (though over
half of that is 6 indexes). My best idea so far has been to display
one "page" of the database at time, where a page is some arbitrary
number (20K for now). I'm using the LIMIT clause to get a particular
page of results. Some sample query results are below:
$ time sqlite3 testdb 'SELECT file_name, file_size FROM File LIMIT
100000;' > /dev/null
real 0m4.167s
user 0m0.645s
sys 0m0.837s
$ time sqlite3 testdb 'SELECT count(*) FROM File LIMIT 100000;' > /
dev/null
real 9m1.460s
user 0m7.307s
sys 0m18.270s
$ time sqlite3 testdb 'SELECT file_name, file_size FROM File LIMIT
2000000, 20000;' > /dev/null
real 12m43.634s
user 0m6.670s
sys 0m19.800s
FYI, SQLite's default_page_size is set to 50,000 for this db. To the
best of my knowledge, using explain on these queries shows they are
using the rowid index.
Because it can take so long to get a count of the records, I cache
the record count within the database and let the user refresh it at
his/her option. Moving between pages is faster, but still sluggish.
So, I'm looking for suggestions on how to improve the performance of
a count(*) and of LIMIT x,y.
One suggestion I had was to split the table into two with the most
relevant data in one table and additional data stored in the second
table with a one-to-one relationship. The idea is to reduce the
amount of file storage SQLite would have to read to be able to
complete a count or limit. Given SQLite's implementation, can I
expect a large improvement? I expect that if nothing else, I'll be
using much less of SQLite's cache, which would be significant.
Is there a better way than the LIMIT clause to get a block of records?
Thanks for reading,
Aaron
