## SQL As Understood By SQLite

# ANALYZE

#### analyze-stmt:

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

The default implementation stores all statistics in a single table named sqlite_stat1. If SQLite is compiled with the SQLITE_ENABLE_STAT2 option, then additional histogram data is collected and stored in sqlite_stat2. Future enhancements may create additional tables with the same name pattern except with the "1" or "2" changed to a different digit.

The DROP TABLE command does not work on the sqlite_stat1 or sqlite_stat2 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. 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.