SQLite / Intermediate: query structure and transactional design / Lesson 9

Indexes, expression indexes, and partial indexes

Design SQLite indexes around concrete predicates, output needs, and write costs while using expression and partial-index rules accurately.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Explain composite-key order and covering access.
Use matching deterministic expressions.
Apply partial indexes to a documented subset.

An index is maintained data, not a free hint

An index stores an ordered access structure that can reduce work for suitable searches and ordering. It occupies space and must be maintained during relevant inserts, updates, and deletes. An index that helps a rare report may slow a frequent write path. Start with actual predicates, result size, sorting requirements, and workload frequency rather than adding an index to every column.

In ordinary rowid tables, secondary indexes include row-location information; INTEGER PRIMARY KEY identity and WITHOUT ROWID storage change design details. Do not import SQL Server clustered-index or INCLUDE syntax into SQLite. A covering index can supply required values without extra table lookups, but adding every projected column makes maintenance and storage more expensive.

Put equality and range needs into a candidate key

For a customer-specific date range, an index beginning with customer_id and then created_on is a reasonable candidate. Subsequent columns can support output needs or tie ordering under the documented structure. The first script creates a small index and returns one known order; it verifies query semantics, not a universal latency improvement. A tiny table can legitimately be scanned.

Leftmost-key rules, inequalities, ordering, and selectivity affect usable access. An index on(a,b) does not automatically serve every query on b as if b were the first key. Some planner strategies can use additional information, but those are reasons to inspect evidence rather than promise a seek. Check existing overlapping indexes before adding another candidate.

sql A candidate composite access path

CREATE TABLE purchase(id INTEGER PRIMARY KEY,customer_id INTEGER,created_on TEXT,amount INTEGER);
INSERT INTO purchase VALUES(1,1,'20260101',10),(2,1,'20260102',20),(3,2,'20260103',99);
CREATE INDEX purchase_customer_date ON purchase(customer_id,created_on,amount);
SELECT id,amount FROM purchase WHERE customer_id=1 AND created_on>='20260102'
ORDER BY created_on,id;

Expected output

id|amount
2|20

The query returns order2 with amount20. The example does not invent a timing gain or require the optimizer to choose a particular plan.

Match the expression and the indexed subset

An expression index can support a query using the indexed expression under SQLite's matching rules. The planner does not generally perform arbitrary algebra to prove two different expressions equivalent. Use the documented expression consistently, and respect restrictions such as deterministic functions. An index on lower(name) does not turn all text comparisons into a universal Unicode normalization system.

A partial index contains only rows satisfying its WHERE predicate. The second script enforces one active account per email while allowing an inactive historical row with the same email. This is a business uniqueness rule, not merely a speed trick. A query must imply the indexed subset through the planner's supported reasoning before that index is safely applicable.

sql Expression lookup and conditional uniqueness

CREATE TABLE account(id INTEGER PRIMARY KEY,name TEXT,email TEXT,active INTEGER NOT NULL CHECK(active IN(0,1)));
CREATE INDEX account_lower_name ON account(lower(name));
CREATE UNIQUE INDEX account_active_email ON account(email) WHERE active=1;
INSERT INTO account VALUES(1,'AYA','a@example.test',1),(2,'aya','a@example.test',0),(3,'Omar','b@example.test',1);
SELECT id,name FROM account WHERE lower(name)='aya' ORDER BY id;
SELECT email,COUNT(*) AS copies FROM account GROUP BY email HAVING COUNT(*)>1 ORDER BY email;

Expected output

id|name
1|AYA
2|aya

email|copies
a@example.test|2

Both spellings match lower(name). The repeated email is legal because only one corresponding row is active.

Keep search expressions compatible with storage

Applying a function or conversion to an indexed column can prevent a useful direct range, unless an appropriate matching expression index exists. For consistently encoded timestamps, a half-open range can expose contiguous values without extracting a month from every row. Parameter and stored-value types still matter under affinity rules. Inspect the query actually sent by the application, not a different hand-written approximation.

A wide qualifying range may make a scan appropriate even with a usable index. Labels such as SEARCH, SCAN, and COVERING need context: how many rows qualify, which values are needed, and what work remains? Avoid forcing INDEXED BY as routine tuning; it is a stronger contract with failure consequences when the specified index cannot be used.

Measure a complete read/write tradeoff

Validate identical result keys and values before comparing plans. Then compare representative distributions, query frequency, write cost, database size, and concurrent transaction duration in an isolated environment. A tiny synthetic example explains structure but cannot establish production selectivity. Record the runtime and statistics context because planner choices can change across versions and data changes.

Remove an index only after checking uniqueness enforcement and dependent workloads. Two indexes that look similar may enforce different partial rules or orderings. Keep migrations reversible through a tested recovery plan and retain the reason each index exists. The best design is a small justified set supporting actual invariants and access paths, not the largest number of available indexes.

Common errors and fixes

An indexed expression is not used as expected.

Why it happens: The query expression or collation does not match the indexed contract.
How to fix it: Compare the actual expression and inspect the plan.
Prevent it next time: Test the application SQL and parameter types.

A partial UNIQUE rule rejects an unexpected row.

Why it happens: The row belongs to the indexed subset and conflicts there.
How to fix it: Review the business subset and key rather than removing the rule blindly.
Prevent it next time: Test active and inactive transitions.

Writes slowed after adding many indexes.

Why it happens: Each change maintains additional structures.
How to fix it: Review overlapping candidates and measure the workload tradeoff.
Prevent it next time: Include write and storage budgets in index proposals.

Check your understanding

1. Why does an index on customer_id,created_on not promise optimal access for a created_on-only query?

Show worked solution

The leftmost key and actual predicate shape matter; the index is ordered by customer first. Planner alternatives and statistics may help particular cases, but the design must be assessed with a real plan and workload rather than assuming independent indexes on every component.

2. What does UNIQUE(email) WHERE active=1 allow and forbid?