Indexes

As RecipeBox grows to thousands of recipes and reviews, queries that once felt instant start to slow down — because without help, SQLite has to scan every single row to find the ones you want. An index is a separate, sorted structure SQLite maintains alongside a table, built on one or more columns, so it can jump straight to matching rows instead of scanning the whole table. This lesson covers creating indexes, unique indexes, SQLite's partial indexes, and the tradeoff every index comes with.

CREATE INDEX

An index works conceptually the same way it does in any relational database: it speeds up lookups, filtering, and sorting on the indexed column(s), at the cost of slightly slower writes and extra disk space (covered below). RecipeBox's reviews table links a recipe_id to every review — and "get all reviews for this recipe" is one of the most common queries in the app.

-- Speed up "find all reviews for a given recipe"
CREATE INDEX idx_reviews_recipe_id
ON reviews(recipe_id);

-- Now this query can use the index instead of scanning every review
SELECT rating, comment
FROM reviews
WHERE recipe_id = 42;

The index name (idx_reviews_recipe_id) is entirely up to you — a common convention is idx_<table>_<column> so it's obvious what each index is for at a glance.

CREATE UNIQUE INDEX

A UNIQUE index does everything a regular index does, plus it rejects any insert or update that would create a duplicate value in the indexed column(s). It's the same mechanism SQLite uses under the hood to enforce a column's UNIQUE constraint.

-- No two users may register with the same email
CREATE UNIQUE INDEX idx_users_email
ON users(email);

-- A composite unique index: one review per user, per recipe
CREATE UNIQUE INDEX idx_reviews_user_recipe
ON reviews(user_id, recipe_id);

Partial Indexes

SQLite lets you index only the rows that match a condition, by adding a WHERE clause to the index definition — a genuinely nice SQLite feature not every database offers. A partial index is smaller and cheaper to maintain than a full index, which matters when most rows never appear in the queries you're optimizing for.

-- Only index recipes that are actually published (skip drafts)
CREATE INDEX idx_recipes_published_category
ON recipes(category)
WHERE is_published = 1;

-- Only index reviews that have a written comment (many are rating-only)
CREATE INDEX idx_reviews_with_comment
ON reviews(recipe_id)
WHERE comment IS NOT NULL;

The Tradeoff: Reads vs. Writes vs. Disk

Indexes are not free. Every index you add: