On Mon, Aug 2, 2010 at 2:11 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> > On 2 Aug 2010, at 7:06pm, Richard Hipp wrote: > > > Most of the slow queries seem to be "SELECT count(*) FROM ....". Such > > queries have to visit every row in the table (in order to count the rows) > > and thus get progressively slower as the number of entries in the > database > > increases. > > Might there be a way to optimize count() ? After all, the number of rows > in a table is probably held somewhere convenient. > SQLite does not track the number of rows in a table. When you request a count, SQLite has to count the rows. It is possible to do a count a contiguous subset of rows (all rows between two bounding keys) in O(logN) time using a B-tree, and I considered adding that capability while designing the SQLite file format (in 2004). But I rejected the idea since maintaining the extra information takes up space and also requires CPU and I/O to maintain even if it is never used, thus forcing all users to pay a space and time penalty even if they never do a count. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- --------------------- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
