http://www.sqlite.org/optoverview.html section 6.2 "Manual Control Of Query Plans Using SQLITE_STAT Tables"
SQLite provides the ability for advanced programmers to exercise control over the query plan chosen by the optimizer. One method for doing this is to fudge the ANALYZE results in the sqlite_stat1, sqlite_stat3, and/or sqlite_stat4 tables. That approach is not recommended except for the one scenario described in the next paragraph. For a program that uses an SQLite database as its application file-format, when a new database instance is first created the ANALYZE command is ineffective because the database contain no data from which to gather statistics. In that case, one could construct a large prototype database containing typical data during development and run the ANALYZE command on this prototype database to gather statistics, then save the prototype statistics as part of the application. After deployment, when the application goes to create a new database file, it can run the ANALYZE command in order to create the statistics tables, then copy the precomputed statistics obtained from the prototype database into these new statistics tables. In that way, statistics from large working data sets can be preloaded into newly created application files. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke Sent: Wednesday, October 04, 2017 12:06 PM To: SQLite mailing list Subject: Re: [sqlite] PRAGMA optimize; == no busy handler? > So why not work around the whole process ? On your development system, > create a database with plausible data in. Run ANALYZE. Then copy all the > tables named "sqlite_stat*" into a new database. That’s an interesting idea. I’ve been assuming that ANALYZE was dependent on the exact contents of the database, but if its results can be canned and applied to new databases, that’s a good optimization. I’m just a bit wary because this isn’t documented anywhere, and generally one is warned against writing to `sqlite_*` tables. I’d rather have some official blessing of this technique before I consider using it. —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
