SQLite: ANALYZE Command

This SQLite post explains how to use the SQLite ANALYZE command with syntax and examples.

Description

The SQLite ANALYZE command is used to calculate and store statistical information about the tables and indexes analyzed. The statistical information gathered will be stored in a system table called sqlite_stat1 and will be later used by the query optimizer to determine the most efficient query plan.

Syntax

The syntax for the ANALYZE command in SQLite is:

ANALYZE [database_name][.table_name];

Parameters or Arguments

database_name

Optional. The name of the database to analyze.

table_name

Optional. The name of the table to analyze.

Note

• The statistical information found in the sqlite_stat1 table is not automatically updated. So if the data in your tables has changed significantly, it is a good idea to rerun the ANALYZE command. Otherwise, the query optimizer might not select the most efficient query plan.

Example - Analyze Indexes in all Databases

The ANALYZE command is most commonly run with no parameters. When no parameters are provided, the ANALYZE command will gather statistical information about all of the indexes in all of the attached databases.

For example:

ANALYZE;

This example would store all of the statistical information for all indexes in the sqlite_stat1 system table.

Example - Analyze Indexes in a Database

If you specify ANALYZE with a database name, the ANALYZE command will gather statistical information for all of the indexes in the specified database.

For example:

ANALYZE AODBA;

This example would analyze all of the indexes in the database called AODBA and store the statistical information in the sqlite_stat1 system table.

Example - Analyze Indexes in a Table

Finally, if you can specify ANALYZE with a table name, the ANALYZE command will gather statistical information for all of the indexes in the specified table.

For example:

ANALYZE suppliers;

This example would analyze all of the indexes in the table called suppliers and store the statistical information in the sqlite_stat1 system table.