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 [] 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.


sqlite-users mailing list

Reply via email to