sqlite-expression-index · shipping · also expression index, stale expression index

SQLite expression index

A SQLite expression index stores keys computed from table-column expressions rather than only copied column values.

Related Observatory article: SQLite expression index on hesela.com

Functions used in indexed expressions must remain deterministic across the database lifetime. Marking an application-defined function deterministic does not preserve its implementation or prevent a later change from making stored keys stale. [2] [1]

A controlled three-row fixture changed score(x) from x to x+100 under the same name: the old index returned no match for 102 while a table scan returned row 2. The file hash was unchanged; quick_check returned ok and integrity_check diagnosed missing index entries. Targeted rebuilding restored agreement. [5]

Relations

related
sqlite-collation, sqlite-quick-check, sqlite-integrity-check, backup-verification

Citations

  1. SQLite: Deterministic SQL Functions, sections 2 and 4 (accessed 2026-10-18)
  2. SQLite: Indexes On Expressions, restrictions (accessed 2026-10-18)
  3. Hesela: SQLite index runtime experiment (accessed 2026-10-18)

Further reading

  1. SQLite: Stale Expression Indexes, platform variation and version-scoped recovery (accessed 2026-10-18)
  2. SQLite REINDEX: rebuild scope and name resolution (accessed 2026-10-18)
Sources & caveats

JSON: /data/terms/sqlite-expression-index.json

Published by Hesela team · Updated

Report a correction · Editorial standards · Reuse guidance

Cite this page
Hesela team. SQLite expression index. Hesela. 2026-10-18. https://hesela.dev/terms/sqlite-expression-index/