SQLite Basics: Match Queries to Expression Indexes

Filtering on a calculated value does not have to mean storing that value as a separate application-maintained column. SQLite can index an expression such as lower(code) or abs(offset). The important part is using a matching expression in the query.

Expression indexes require SQLite 3.9.0 or later. They may use columns from their own table and deterministic functions, but not subqueries or values from other tables. Read the expression-index reference before adding one to a database opened by older clients.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. It prints pipe-separated rows and displays missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

Each invocation starts with a fresh in-memory database. The examples are independent, and the toy data disappears when the connection closes.

The EXPLAIN QUERY PLAN rows are the raw output observed on SQLite 3.53.1. Plan identifiers, wording, and choices can change. These tiny fixtures demonstrate matching rules, not measured speed improvements.

1. Look up an ASCII code through a normalized expression

Save as index-lowercase-part-codes.sql:

CREATE TABLE parts (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL,
    label TEXT NOT NULL
);
INSERT INTO parts VALUES
    (1, 'AB-10', 'Cable'),
    (2, 'Cd-20', 'Adapter'),
    (3, 'EF-30', 'Stand');
CREATE INDEX parts_lower_code ON parts(lower(code));
EXPLAIN QUERY PLAN
SELECT label FROM parts WHERE lower(code) = lower('CD-20');
SELECT id, code, label FROM parts
WHERE lower(code) = lower('CD-20')
ORDER BY id;

Run:

python3 run-sql.py index-lowercase-part-codes.sql

Output:

3|0|61|SEARCH parts USING INDEX parts_lower_code (<expr>=?)
2|Cd-20|Adapter

The plan uses parts_lower_code because the indexed lower(code) expression appears in the predicate. The selected row preserves the original mixed-case code; the index does not rewrite the stored text.

SQLite’s built-in lower() handles ASCII case conversion, not full Unicode case folding. This example deliberately uses ASCII part codes. International identifiers require a separately defined normalization policy. Also, this is an ordinary index, not a UNIQUE index: two original codes that normalize to the same value would both match.

2. Filter and sort by a calculated magnitude

Save as index-measurement-magnitudes.sql:

CREATE TABLE deviations (
    id INTEGER PRIMARY KEY,
    sensor TEXT NOT NULL,
    offset INTEGER NOT NULL CHECK (offset BETWEEN -1000 AND 1000)
);
INSERT INTO deviations VALUES
    (1, 'A', -12),
    (2, 'A', 4),
    (3, 'A', 18),
    (4, 'B', -30),
    (5, 'A', 12);
CREATE INDEX deviations_magnitude
ON deviations(sensor, abs(offset) DESC, id);
EXPLAIN QUERY PLAN
SELECT id, offset FROM deviations
WHERE sensor = 'A' AND abs(offset) >= 10
ORDER BY abs(offset) DESC, id;
SELECT id, offset, abs(offset) AS magnitude FROM deviations
WHERE sensor = 'A' AND abs(offset) >= 10
ORDER BY abs(offset) DESC, id;

Run:

python3 run-sql.py index-measurement-magnitudes.sql

Output:

4|0|51|SEARCH deviations USING INDEX deviations_magnitude (sensor=? AND <expr>>?)
3|18|18
1|-12|12
5|12|12

The result includes both positive and negative offsets, ordered by distance from zero rather than signed value. Sensor B’s larger deviation is excluded. IDs break the tie between the two measurements with magnitude 12.

The observed plan has no separate temporary sorting step. Still keep ORDER BY: an index’s presence is not a presentation-order contract. The CHECK limits this fixture to small values; abs() on SQLite’s minimum signed 64-bit integer raises an overflow error, so an arbitrary unrestricted integer domain needs additional thought.

3. Keep equivalent arithmetic in the same written form

Save as match-indexed-area-expression.sql:

CREATE TABLE panels (
    id INTEGER PRIMARY KEY,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    label TEXT NOT NULL
);
INSERT INTO panels VALUES
    (1, 6, 7, 'Small'),
    (2, 3, 14, 'Narrow'),
    (3, 8, 8, 'Square');
CREATE INDEX panels_area ON panels(width * height);
EXPLAIN QUERY PLAN
SELECT label FROM panels WHERE width * height = 42;
EXPLAIN QUERY PLAN
SELECT label FROM panels WHERE height * width = 42;
SELECT 'matching expression', id, label FROM panels
WHERE width * height = 42 ORDER BY id;
SELECT 'reversed expression', id, label FROM panels
WHERE height * width = 42 ORDER BY id;

Run:

python3 run-sql.py match-indexed-area-expression.sql

Output:

3|0|61|SEARCH panels USING INDEX panels_area (<expr>=?)
2|0|216|SCAN panels
matching expression|1|Small
matching expression|2|Narrow
reversed expression|1|Small
reversed expression|2|Narrow

Both predicates return the same two small-integer results. Only the first observed plan uses panels_area for the lookup. Reversing the operands changes the expression shape even though multiplication gives the same answer here.

SQLite does not perform general algebraic rewrites to match expression indexes. Choose one expression form and reuse it in query construction. Minor spacing changes are not the issue; changing the expression tree is. Avoid forcing an index to hide a mismatch that a clearer predicate can fix.

The core-function reference documents lower() and abs(). Deterministic-function rules explain why an indexed function must remain consistent: falsely labeling a changing application function deterministic can leave index contents invalid.

Expression indexes consume storage and add write maintenance. Inspect the plan, then benchmark representative data. Use the EXPLAIN QUERY PLAN guide to interpret diagnostics rather than treating a SEARCH label as a stopwatch.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py index-lowercase-part-codes.sql index-measurement-magnitudes.sql match-indexed-area-expression.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.