Indexes in SQLite: What Actually Makes Your Queries Fast

SQLite is fast on small data and gets slow on large data if you ignore indexes. The good news: the rules are simple, and SQLite tells you exactly what to index.

When an index helps

An index speeds up lookups on a specific column — WHERE, ORDER BY, JOIN, and GROUP BY on that column. The rule of thumb:

• A table with a few thousand rows does not need hand-built indexes — the whole table fits in memory and a full scan is fine.
• As rows grow past tens of thousands, the right index is the difference between milliseconds and seconds.
• Put an index on any column you filter or sort by frequently: WHERE status = ?, ORDER BY published_at, WHERE user_id = ?.

When an index hurts

Every index makes writes slower and takes disk space — each INSERT/UPDATE/DELETE must maintain every index. Indexing everything is how you end up with slow writes and a bloated file. Index the columns you actually query, not the ones you might.

How to find what needs one

Use EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN SELECT * FROM posts WHERE status = 'published' ORDER BY published_at DESC;

If the plan says SCAN TABLE posts (full table scan), and the table is large, an index on status (or a composite) is the fix. If it says SEARCH, you are already covered.

The practical recipe

1. Start with no hand-built indexes beyond the primary keys.
2. When a query is slow, run EXPLAIN QUERY PLAN.
3. Add exactly the index the plan asks for.
4. Measure again. Repeat rarely.

Indexes are a search problem, not a decoration problem. SQLite gives you the tool to find them — use it before you guess. Sizing the file your indexes will live in? The estimator below counts index overhead too.