SQLite: ANALYZE Command
This SQLite tutorial 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 techonthenet;
This example would analyze all of the indexes in the database called techonthenet 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.
Advertisements