#readwise # SQLite ANALYZE  ## Metadata - Author: [[sqlite.org]] - Full Title: SQLite ANALYZE - URL: https://www.sqlite.org/lang_analyze.html ## Summary The `ANALYZE` command collects statistics about tables and indexes in a database to improve query planning. It's important to run the `PRAGMA optimize` command periodically, especially for applications with long-lived connections. Since SQLite version 3.46.0, this command runs `ANALYZE` only when necessary, making it faster for large databases. Additionally, setting an analysis limit can help `ANALYZE` run faster while still providing useful statistics. ## Highlights **The `ANALYZE` command gathers statistics about tables and indices and stores the collected information in [internal tables](https://www.sqlite.org/lang_analyze.html/fileformat2.html#intschema) of the database where the query optimizer can access the information and use it to help make better query planning choices.** ([View Highlight](https://read.readwise.io/read/01jjh4vkgmfpys35ggrj1vc82x)) ^iri5gk --- **The use of `ANALYZE` is never required. However, if an application makes complex queries that have many possible query plans, the query planner will be better able to pick the best plan if `ANALYZE` has been run.** This can result it significant performance improvements for some queries. ([View Highlight](https://read.readwise.io/read/01jjh4w2ztk5t1svs6wzmjhn44)) --- - **The [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) command will automatically run `ANALYZE` when needed.** Suggested use: ^naxqbp 1. **Applications with short-lived database connections should run `PRAGMA optimize;` once, just prior to closing each database connection.** 2. **Applications that use long-lived database connections should run `PRAGMA optimize=0x10002;` when the connection is first opened, and then also run `PRAGMA optimize;` periodically, perhaps once per day, or more if the database is evolving rapidly.** 3. **All applications should run `PRAGMA optimize;` after a schema change, especially after one or more [CREATE INDEX](https://www.sqlite.org/lang_analyze.html/lang_createindex.html) statements**. The [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) command is usually a no-op but it will occasionally run one or more `ANALYZE` subcommands on individual tables of the database if doing so will be useful to the query planner. Since SQLite version 3.46.0 (2024-05-23), the `PRAGMA optimize` command automatically limits the scope of `ANALYZE` subcommands so that the overall `PRAGMA optimize` command completes quickly even on enormous databases. There is no need to use [PRAGMA analysis_limit](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_analysis_limit). This is the recommended way of running `ANALYZE` moving forward. The [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) command will normally only consider running `ANALYZE` on tables that have been previously queried by the same database connection or that do not have entries in the [sqlite_stat1](https://www.sqlite.org/lang_analyze.html/fileformat2.html#stat1tab) table. However, if the `0x10000` bit is added to the argument, `PRAGMA optimize` will examine all tables to see if they can benefit from `ANALYZE`, not just those that have been recently queried. There is no query history when a database connection first opens, and that is why adding the `0x10000` bit is recommended when running `PRAGMA optimize` on a fresh database connection. ([View Highlight](https://read.readwise.io/read/01jjh4zkrvmqk987btj0jnb7s8)) --- Running `ANALYZE` can cause SQLite to choose different query plans for subsequent queries. This is almost always a positive thing, as the query plans chosen after `ANALYZE` will in nearly every case be better than the query plans picked before `ANALYZE`. That is the whole point of `ANALYZE`. ([View Highlight](https://read.readwise.io/read/01jjh50sxqbdqymt71s5pmt87a)) --- The [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) command will automatically run `ANALYZE` on individual tables on an as-needed basis. The recommended practice is for applications to invoke the [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) statement just before closing each database connection. Or, if the application keeps a single database connection open for a long time, then it should run `PRAGMA optimize=0x10002` when the connection is first opened and run `PRAGMA optimize;` periodically thereafter, perhaps once per day or even once per hour. Each SQLite [database connection](https://www.sqlite.org/lang_analyze.html/c3ref/sqlite3.html) records cases when the query planner would benefit from having accurate results of `ANALYZE` at hand. These records are held in memory and accumulate over the life of a database connection. The [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) command looks at those records and runs `ANALYZE` on only those tables for which new or updated `ANALYZE` data seems likely to be useful. In most cases [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) will not run `ANALYZE`, but it will occasionally do so either for tables that have never before been analyzed, or for tables that have grown significantly since they were last analyzed. Since the actions of [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) are determined to some extent by prior queries that have been evaluated on the same database connection, it is recommended that [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) be deferred until the database connection is closing and has thus had an opportunity to accumulate as much usage information as possible. It is also reasonable to set a timer to run [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) every few hours, or every few days, for database connections that stay open for a long time. When running [PRAGMA optimize](https://www.sqlite.org/lang_analyze.html/pragma.html#pragma_optimize) immediately after a database connection is opened, one can add the 0x10000 bit to the bitmask argument (thus making the command read `PRAGMA optimize=0x10002`) which causes all tables to be examined, even tables that have not been queried during the current connection. ([View Highlight](https://read.readwise.io/read/01jjh5ch907rgqckp7v7zg6sbs)) ---