On Thu, Dec 08, 2005 at 12:26:44PM -0500, Teg wrote: > I have a 6.5 gb database with 29,587 records in it. It takes about > 30-40 seconds for a count to return the first time I specify one in > "Sqlite3" (seeing this in my program as well). Subsequent "count" > operations are nearly instantaneous even when I exit Sqlite3 and > restart. I'm pretty much seeing that across the board with other DB's > as well, that count take a fairly long time the first time it's called > and then it's fast. > > Is there any way I can speed this up? I use "count" to set my progress > bars to proper ranges so, the delay causes people to wait.
Most likely the time is being spent reading parts of the database from disk into memory buffers. After it is read once, the OS caches it if you have enough spare RAM. If you want it to be faster, your goal should be to reduce the amount of data that has to be read. If you give count() an argument, it counts the number of times that argument is non-null. Because you are using count(FileOrder) and there is no index on this column, the entire database (minus the overflow pages from your blobs) is read. So your goal will be to come up with a "select count()" statement that doesn't need to read as much data. Try using ".explain" then "explain select count(FileOrder) from Files_V1;" to see what is happening. Determine if it is using an index, or reading the whole table. Then try "select count(*) from Files_V1" and see if that is much faster. If not, try doing the count on Signature to see if you can force it to use the index. Failing that, you could always make a small table of just FileOrder and do the count on that. Good luck! --nate
