Richard and Scott - big thanks for the suggestions. -Mike
----- Original Message ----- From: "Scott Hess" <sh...@google.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Monday, August 2, 2010 1:45:26 PM Subject: Re: [sqlite] Optimizing Songbird [Sorry for the mis-fire.] On Mon, Aug 2, 2010 at 11:11 AM, 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. I worked with a group that had to do this, once, and doing this using triggers isn't too hard. As a bonus, it is easy to adapt the technique to keeping counts of subsets of the data which meet various conditions. AFAICT, it's possible that this was just as efficient as having it built in, because the trigger is effectively inlined into the update statements which affect the table. I did find myself wondering whether it wouldn't be useful to have alternate syntax in table creation for handling this, kind of like an index. Then the cost would mainly be isolated to the tables which need it (with a slight incremental cost to the parser). -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
