---
source: https://iinventedlight.com/library/notes/sqlite-query-planner-indexes.md
title: "SQLite indexes and the query planner"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "Public domain (sqlite.org); prose restates cited source lines"
attribution: "Summary by iinventedlight (AI-assisted). Sources: SQLite (sqlite.org), Query Planning, Public domain (sqlite.org); SQLite (sqlite.org), CREATE INDEX, Public domain (sqlite.org)."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run for this note; only the fetched documentation pages were read."
category: coding-references
topic_area: "code-ai-eng: SQLite"
tags: ['sqlite', 'sql', 'indexes', 'performance']
sources:
  - id: S1
    title: "Query Planning"
    publisher: SQLite (sqlite.org)
    url: https://www.sqlite.org/queryplanner.html
    accessed: 2026-11-04
    license: "Public domain (sqlite.org)"
  - id: S2
    title: "CREATE INDEX"
    publisher: SQLite (sqlite.org)
    url: https://www.sqlite.org/lang_createindex.html
    accessed: 2026-11-04
    license: "Public domain (sqlite.org)"
---
# SQLite indexes and the query planner

**Answer:** 

```sql
CREATE INDEX idx_fruit_state ON fruitsforsale(fruit, state);

EXPLAIN QUERY PLAN
SELECT price FROM fruitsforsale WHERE fruit = 'Orange' AND state = 'CA';
-- SEARCH fruitsforsale USING INDEX idx_fruit_state (fruit=? AND state=?)
```

## Index tips

- A covering index holding every needed column avoids the table lookup.
- Partial indexes with `WHERE` index only some rows.
- Indexes can be on expressions and use ASC or DESC.
- `CREATE INDEX IF NOT EXISTS` is a no-op if it already exists.

Run `ANALYZE` so the planner has statistics.

## Claims
- id: C1
  claim: The SQLite query planner usually does a good job.
  source: S1
  evidence: "Most of the time, the query planner in SQLite does a good job."
- id: C2
  claim: Indices must normally be added by programmers.
  source: S1
  evidence: "These indices must normally be added by programmers."
- id: C3
  claim: One normally tries to avoid full table scans.
  source: S1
  evidence: "For that reason, one normally tries to avoid full table scans."
- id: C4
  claim: A WHERE clause makes the index a partial index.
  source: S2
  evidence: "If the optional WHERE clause is included, then the index is a"

## Sources
- [S1] Query Planning. SQLite (sqlite.org). https://www.sqlite.org/queryplanner.html . License: Public domain (sqlite.org). Accessed 2026-11-04.
- [S2] CREATE INDEX. SQLite (sqlite.org). https://www.sqlite.org/lang_createindex.html . License: Public domain (sqlite.org). Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).