"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
