Very interesting development, thanks for pushing the boundaries at each new release!
Would it be possible to consider some form of deferred optimize? ie. rather than optimize when closing the connection, it would just write the optimize info gathered during the heavy queries, for use in a future optimize. The rational is that heavy queries can come over several hours (and multiple DB connections). During those "rush hours" running an ANALYZE could be quite detrimental when the databases are large (even for read-only queries, it would affect the service performance through I/O usage), and in my particular usage case, somewhat less efficient queries are less of a problem than stalling the service. Once the rush hours have passed, the optimize would be run with the previously collected data (I currently run a regular ANALYZE outside of rush hours, but I gather it is only rarely beneficial). Eric On Tue, Mar 7, 2017 at 4:22 AM, jose isaias cabrera <jic...@barrioinvi.net> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
