ANALYZE and PRAGMA optimize

~12 min · analyze, stats, optimizer

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Help the planner make better choices

SQLite's query planner uses statistics stored in sqlite_stat1 to decide which index to use. Without stats, it makes reasonable defaults; with stats, it can pick dramatically better plans for skewed data.

Two commands you should know:

ANALYZE — gather statistics for the entire database (or a single table). Run after a major data shape change, or as part of a deploy step.
PRAGMA optimize — modern, lightweight version. SQLite tracks which tables changed enough to need re-analyzing and only re-runs those. Cheap to call on every connection close.

Tip: The recommended modern pattern: call PRAGMA optimize on each connection close. SQLite figures out what (if anything) needs work; usually the call is near-free. This keeps stats fresh in long-running apps without a maintenance window.

Code

Three patterns for keeping stats fresh·sql

-- One-shot, after a big import
ANALYZE;

-- Single-table
ANALYZE messages;

-- Recommended: PRAGMA optimize on connection close
-- (does nothing if no table changed enough since the last analyze)
PRAGMA optimize;

Python — wire optimize into your connection lifecycle·python

import sqlite3, atexit

conn = sqlite3.connect('demo.db')
conn.execute('PRAGMA journal_mode = WAL')

def on_close():
    try:
        conn.execute('PRAGMA optimize')
    finally:
        conn.close()

atexit.register(on_close)

External links

Exercise

On a database with at least 100k rows, run a multi-index query with EXPLAIN QUERY PLAN. Then run ANALYZE and re-EXPLAIN. Note whether the planner picked a different index. Then add a long-running app pattern: every connection close calls PRAGMA optimize; observe over time that stats stay fresh without manual maintenance.

ANALYZE and PRAGMA optimize

Help the planner make better choices

Code

External links

Exercise

Progress

Comments 0