Add a useful index

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

CREATE INDEX notes_title_idx ON notes(title);

An index is a second data structure ordered by the indexed value. SQLite can search that smaller structure and use the stored row reference instead of examining every row in notes.

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.

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

CREATE INDEX notes_owner_created_idx
ON notes(owner_id, created_at);

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

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 may be 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.