← All notes

Answer:

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).

@misc{iil_note_e3adge9v,
  author = {{iinventedlight}},
  title = {{SQLite indexes and the query planner}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/sqlite-query-planner-indexes/},
  note = {Permanent ID iil:note:e3adge9v. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/sqlite-query-planner-indexes.md.