Add a useful index

An index is a second data structure, ordered by the indexed column, that lets SQLite find rows without scanning the whole table. Every index also makes writes slower, because SQLite must update the index on each insert, update, or delete.

Add one only when you have evidence that a lookup runs often enough to justify the cost.

A simple index on one column

If the application frequently finds notes by title, an index can avoid scanning every row:

CREATE INDEX notes_title_idx ON notes(title);

Verify the access plan before and after creating it:

EXPLAIN QUERY PLAN
SELECT id, title FROM notes WHERE title = 'SQLite checklist';

On an unindexed table you will usually see a table scan. After creating the index, the plan should mention searching with notes_title_idx. The plan is evidence; the presence of an index in the schema is not proof that a query uses it.

Compound indexes and column order

For a query with more than one filter, column order matters:

CREATE INDEX notes_owner_created_idx
ON notes(owner_id, created_at);

This helps queries that constrain owner_id and optionally sort or filter by created_at. It generally does not help a query that only constrains created_at, because the leading indexed column is missing.

When not to index

Indexes are not free. Every insert, relevant update, and delete must update them. They also make the database file larger. A low-cardinality column such as a boolean flag is often a poor standalone index when most rows share the same value.

Add indexes for observed access patterns: frequent filters, joins, uniqueness requirements, and ordering that matters. Remove indexes that duplicate another index’s leading columns unless a measured workload justifies both.

Try it: create 10,000 notes owned by several users. Compare the query plan for WHERE owner_id = ? ORDER BY created_at before and after adding the compound index.