Richard Hipp wrote...
On 3/6/17, jose isaias cabrera <jic...@barrioinvi.net> wrote:
Richard Hipp wrote...
Have you ever wondered when you should run ANALYZE on an SQLite
database? It is tricky to figure out when that is appropriate. The
Thanks for this. I actually run this ANALYZE weekly with a script. This
will be better. I can run it everyday don't do any harm. Thanks.
Thanks for letting me know. I don't know if this applies in your case
or not, but reading your note made me realize that the documentation
might be misleading and/or unclear as written.
The "PRAGMA optimize" command should be run from the same database
connection that is doing the heavy queries. The reason for this is
that the database connection remembers (in RAM) specifically which
tables and indexes it has considered for use and will only run ANALYZE
on those tables for which some prior query would have benefited from
having good sqlite_stat1 numbers during the current session. That is
why "PRAGMA optimize" should be run as the database connection is
closing, rather than when it is first opened.
So it is not (currently) helpful to run "PRAGMA optimize" from a
separate connection, or a connection that is mostly idle. It needs to
be the connection that is actually doing the interesting queries so
that SQLite can know which tables need to be analyzed.
This is exactly how it's going to be used...
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
