SQL As Understood By SQLite

[Top]

ANALYZE

analyze-stmt:

The ANALYZE command gathers statistics about tables and indices and stores the collected information in internal tables of the database where the query optimizer can access the information and use it to help make better query planning choices. If no arguments are given, all attached databases are analyzed. If a database name is given as the argument, then all tables and indices in that one database are analyzed. If the argument is a table name, then only that table and the indices associated with that table are analyzed. If the argument is an index name, then only that one index is analyzed.

The default implementation stores all statistics in a single table named "sqlite_stat1". If SQLite is compiled with the SQLITE_ENABLE_STAT3 option, then additional histogram data is collected and stored in sqlite_stat3. Older versions of SQLite would make use of the sqlite_stat2 table when compiled with SQLITE_ENABLE_STAT2 but all recent versions of SQLite ignore the sqlite_stat2 table. Future enhancements may create additional internal tables with the same name pattern except with final digit larger than "3".

The ALTER TABLE command does not work on the sqlite_stat1 or sqlite_stat3 tables, but all the content of those tables can be queried using SELECT and can be deleted, augmented, or modified using the DELETE, INSERT, and UPDATE commands. The DROP TABLE command works on sqlite_stat1 and sqlite_stat3 as of SQLite version 3.7.9. Appropriate care should be used when changing the content of the statistics tables as invalid content can cause SQLite to select inefficient query plans. Generally speaking, one should not modify the content of the statistics tables by any mechanism other than invoking the ANALYZE command.

Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.

The query planner might not notice manual changes to the sqlite_stat1 and/or sqlite_stat3 tables. An application can force the query planner to reread the statistics tables by running ANALYZE sqlite_master.