ANALYZE Command

ANALYZE collects statistics that help SQLite choose better query plans.

It is especially useful after major data distribution changes.

Core Concepts

flowchart LR
  A[Data distribution changes] --> B[Run ANALYZE]
  B --> C[sqlite_stat tables updated]
  C --> D[Planner makes better index choices]

Command	Scope
ANALYZE;	Analyze all relevant tables/indexes
ANALYZE table_name;	Analyze one table
ANALYZE index_name;	Analyze one index

Whole Database

ANALYZE;

Expected output
Statistics refreshed for planner use.

Targeted Analyze

ANALYZE orders_idx;

Expected output
Statistics updated for orders_idx-related objects.

Code Examples

-- Refresh planner stats.
ANALYZE;

Expected output
Command completes with no result rows.

-- Inspect whether sqlite_stat1 has entries.
SELECT tbl, idx, stat
FROM sqlite_stat1
ORDER BY tbl, idx;

tbl	idx	stat
table/index names	index name or null	planner stats text

SQLite-Specific Nuances

SQLite Nuance

SQLite stores analyze results in internal sqlite_stat* tables used by the query planner.

These stats are not business data, but they influence performance decisions.

Common Pitfalls / Best Practices

Pitfall

Adding indexes but never running ANALYZE after major data changes, then assuming planner choices will always stay optimal.

Best Practice

Run ANALYZE after bulk imports or large churn, and pair it with query-plan checks for critical queries.

Quick Challenge

Run a targeted analyze for table orders_idx and show one row from sqlite_stat1 for that table.

View Solution

ANALYZE orders_idx;

SELECT tbl, idx, stat
FROM sqlite_stat1
WHERE tbl = 'orders_idx'
LIMIT 1;

tbl	idx	stat
orders_idx	index name	stats text