On 17 Aug 2009, at 2:39am, Shane Harrelson wrote: > No. It only collects/updates stats when you explicitly call the > ANALYZE. > > On Sat, Aug 15, 2009 at 2:48 AM, Jim Showalter <j...@jimandlisa.com> > wrote: > >> It doesn't collect those statistics automatically, as part of query >> plan optimization?
Would people please add new text below any existing text they quote ? That way we can follow the thread. If you don't think people need to read existing text before they read your new text, there's no reason to include it in your post, so don't. Shane is right: the table that ANALYZE makes up is only created/ updated by that command. Without that command, SQLite makes some assumptions about the distribution of keys values in each index. The assumptions are generally good, but ANALYZE is better and will give results which will continue to be better until you change the distribution of the data in your tables. But to get back to the original question, ANALYZE should be a better answer to the problem than a programmer trying to set the best index manually. ANALYZE knows better than you which index is better. Not only will ANALYZE allow SQLite to pick the index which does the search faster, but ANALYZE means that SQLite don't have to spend the necessary time working out which index will do the search faster. If you are producing a big dataset, perhaps one intended for read-only use, doing an ANALYZE just before you burn your DVDs (or whatever) will help. You can read some information on how SQLite works out which index to use here http://www.sqlite.org/cvstrac/wiki?p=QueryPlans Which describes a rarely documented SQLite command and otherwise supplements the normal documentation at http://sqlite.org/optoverview.html Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
