Thanks for that explanation. In my app tables are dropped and created and inserted with data continuously, so the recommended use won't quite apply, but I understand better now.
I noticed sqlite_stat1 doesn't get updated if a table is dropped or renamed with ALTER TABLE, so I take it I will have to take care of this myself, particularly with a table rename. RBS -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 06 February 2008 20:27 To: General Discussion of SQLite Database Subject: Re: [sqlite] when to analyze? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Suppose we have a table with some 10 million rows and this table was > analysed, so sqlite_stat1 has the stats of this table then is it worth it to > analyze again after adding say 1000 more rows? The indexing is still the > same, so no indexes are dropped or created. Also the data of the added rows > won't be dramatically different from the existing rows. > My guess it is not worth the time it will take and I could fine out by > experimenting, but maybe somebody has some thoughts about this and could > tell me. > Briefly: Your guess is correct For additional background into why ANALYZE exists and what it accomplishes for you, please see http://www.sqlite.org/cvstrac/wiki?p=QueryPlans SQLite rarely needs to do ANALYZE at all. You can usually get it to pick efficient query plans without having to ANALYZE. The recommended use of ANALYZE, if you use it at all, is to run it once during development on a dataset that is characteristic of the kinds of data your application will store. Retain the results of this ANALYZE as they are found in the sqlite_stat1 table. Then, when you deploy your application and create a new database, run ANALYZE once as soon as the schema is loaded but before any data is added. Such an ANALYZE will take almost no time because your database is empty. Then delete all of the information from the newly created sqlite_stat1 table and replace it with the data you saved from the ANALYZE you ran during development on your sample dataset. The result of this will be that SQLite will plan queries with an eye toward optimizing databases that are typical for your application. -- D. Richard Hipp <[EMAIL PROTECTED]> _______________________________________________ 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