Indexes in SQLite: A Complete Guide with Examples

Abdul Wahab Junaid

2 years ago

There’s a specific moment a lot of developers hit: a query that used to run instantly starts taking a noticeable second or two, and the usual fix is “add an index.” It’s good advice, but it’s also advice that’s easy to apply badly if you don’t actually understand what an index is doing under the hood. SQLite’s indexing system is simpler than what you’ll find in some enterprise databases, but it’s still got real depth once you get past the basics — composite indexes, partial indexes, covering indexes, and the query planner’s decision-making process are all worth understanding properly.

This guide walks through everything you need to know about indexes in SQLite, from the fundamental concept to the practical syntax and the judgment calls that separate a well-indexed database from a bloated, slow one.

What Is an Index?

An index is a separate data structure that SQLite maintains alongside a table, designed to make lookups on specific columns much faster. Think of it like the index at the back of a textbook: instead of reading every page to find mentions of a topic, you jump straight to the relevant pages. Without an index, SQLite has to scan every single row in a table to find matches for a WHERE condition — this is called a full table scan, and it gets slower and slower as your table grows.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL
);

-- Without an index on department, this scans every row
SELECT * FROM employees WHERE department = 'Engineering';

Add an index on the department column, and SQLite can jump directly to matching rows instead of checking every one:

CREATE INDEX idx_department ON employees(department);

Internally, SQLite indexes are implemented as B-trees, a data structure well-suited to fast lookups, range queries, and sorted traversal — which is why indexes help not just with equality filters but also with ORDER BY, range comparisons, and JOIN operations.

Creating a Basic Index

The core syntax:

CREATE INDEX index_name ON table_name(column_name);

A practical example:

CREATE INDEX idx_employees_department ON employees(department);

You can also create an index and skip errors if it already exists, which is handy in scripts you might run more than once:

CREATE INDEX IF NOT EXISTS idx_employees_department ON employees(department);

Unique Indexes

A unique index enforces that no two rows can share the same value in the indexed column (or combination of columns), while also giving you the performance benefits of a regular index.

CREATE UNIQUE INDEX idx_employees_email ON employees(email);

If you try to insert a row with a duplicate email after this index exists, SQLite will reject it with a constraint violation error. This is actually the same mechanism SQLite uses internally to enforce UNIQUE and PRIMARY KEY constraints defined directly in a table’s schema — those constraints are implemented as automatically created unique indexes behind the scenes.

Composite (Multi-Column) Indexes

An index isn’t limited to a single column. You can index multiple columns together, which is especially useful when your queries commonly filter or sort by more than one column at once.

CREATE INDEX idx_department_salary ON employees(department, salary);

Column order matters a great deal here. This composite index is effective for queries that filter on department alone, or on department and salary together, because SQLite can use the leading column(s) of the index directly. It’s generally not useful for queries that filter on salary alone, without also referencing department, because of how B-tree indexes are structured — they’re essentially sorted first by the first column, then by the second within each group of the first.

-- Uses the composite index efficiently
SELECT * FROM employees WHERE department = 'Engineering' AND salary > 90000;

-- Also uses it, just the department part
SELECT * FROM employees WHERE department = 'Engineering';

-- Does NOT use this particular index effectively
SELECT * FROM employees WHERE salary > 90000;

If you frequently query by salary alone too, you’d want a separate index specifically for that column.

Partial Indexes

A partial index only includes rows that match a specified condition, which can make the index smaller, faster to update, and more targeted for specific query patterns.

CREATE INDEX idx_active_employees ON employees(department)
WHERE status = 'active';

This index only covers rows where status = 'active', so if the majority of your queries only ever care about active employees, this index is smaller and more efficient than indexing every row regardless of status. It’s a genuinely underused feature — a lot of developers coming from other databases don’t realize SQLite supports this, and it can meaningfully reduce both index size and maintenance overhead.

-- This query benefits from the partial index
SELECT * FROM employees WHERE department = 'Engineering' AND status = 'active';

Expression Indexes

SQLite also lets you index the result of an expression, not just a raw column value. This is useful for case-insensitive searches, computed values, or anything where you regularly filter on a transformed version of a column.

CREATE INDEX idx_lower_email ON employees(LOWER(email));

With this in place, a query filtering on the lowercase version of the email can actually use the index:

SELECT * FROM employees WHERE LOWER(email) = 'alice@example.com';

Without an expression index like this, SQLite would need to compute LOWER(email) for every row to check the match, which defeats the purpose of indexing.

Covering Indexes

A covering index is one that contains all the columns a particular query needs, meaning SQLite can satisfy the entire query using just the index, without ever touching the actual table data. This is one of the more powerful (and less obvious) performance techniques available.

CREATE INDEX idx_covering ON employees(department, name, salary);

If your query only needs department, name, and salary, and it filters or sorts by department, SQLite can answer the whole query straight from the index:

SELECT name, salary FROM employees WHERE department = 'Engineering';

You can confirm this is happening by checking EXPLAIN QUERY PLAN — a covering index scan typically shows up as a SEARCH ... USING COVERING INDEX in the output, versus a regular index search that still needs to look up the actual table row afterward.

Descending Indexes

By default, indexes are stored in ascending order, but you can specify descending order per column, which matters for certain sort-heavy queries.

CREATE INDEX idx_salary_desc ON employees(salary DESC);

This is particularly useful for queries that sort by that column in descending order and want to avoid a separate sorting step after retrieving rows.

SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

For composite indexes involving mixed sort directions (some columns ascending, some descending), you can specify each column’s direction individually, which helps SQLite avoid an extra sort step for queries whose ORDER BY matches that exact pattern.

CREATE INDEX idx_dept_salary ON employees(department ASC, salary DESC);

Dropping an Index

DROP INDEX index_name;

Or, to avoid an error if it might not exist:

DROP INDEX IF EXISTS idx_employees_department;

Dropping an index removes the extra storage and maintenance overhead it introduces, but obviously removes whatever query speedup it was providing, so this should generally be based on evidence (an index genuinely going unused) rather than a guess.

Viewing Existing Indexes

You can list every index defined on a table:

PRAGMA index_list(employees);

And see the specific columns an index covers:

PRAGMA index_info(idx_department_salary);

You can also look at the full picture, including auto-generated indexes for UNIQUE and PRIMARY KEY constraints, by querying the schema table directly:

SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = 'employees';

Using EXPLAIN QUERY PLAN to Verify Index Usage

Creating an index doesn’t guarantee SQLite will actually use it. It’s always worth confirming:

EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department = 'Engineering';

If the output mentions a SEARCH step using your index name, it’s being used. If it says SCAN, SQLite is doing a full table scan instead, which means either your index isn’t well-suited to the query, or SQLite has decided (often correctly, for small tables) that a scan is cheaper anyway.

Keeping Statistics Fresh with ANALYZE

SQLite’s query planner relies on statistics about the distribution of data in your indexes to decide which index is most selective for a given query. Running ANALYZE periodically — especially after large data changes — helps the planner make better decisions.

ANALYZE;

Without ever running ANALYZE, SQLite falls back to some reasonable default assumptions, but on larger, more complex, or more skewed datasets, the difference can genuinely affect which index gets chosen and how fast your queries run.

The Cost of Indexes

Indexes aren’t free. It’s worth being upfront about the tradeoffs:

Extra storage. Every index takes up disk space proportional to the size of the indexed columns and the number of rows.
Slower writes. Every INSERT, UPDATE, or DELETE that touches an indexed column has to also update the index, which adds overhead. A table with ten indexes will have noticeably slower write performance than the same table with two well-chosen indexes.
Diminishing returns. Indexing every column “just in case” usually isn’t a good strategy — it bloats storage and slows writes without providing proportional query benefits, since most queries only ever use a handful of access patterns.

Common Use Cases

Speeding up WHERE clause filtering on columns frequently used to narrow down results.
Improving JOIN performance, by indexing foreign key columns that are used to match rows between tables.
Enforcing uniqueness, using UNIQUE indexes for things like email addresses or usernames.
Avoiding sort operations, by indexing columns commonly used in ORDER BY clauses, in the matching sort direction.
Targeted indexing with partial indexes, when only a subset of rows are commonly queried (like active vs. archived records).
Case-insensitive or transformed lookups, using expression indexes.

Best Practices

Index columns that appear in WHERE, JOIN, and ORDER BY clauses, not just any column that seems important.
Pay close attention to column order in composite indexes — put the columns used for equality filters before columns used for ranges or sorting.
Use partial indexes when queries consistently filter on a subset of rows, to keep the index smaller and more efficient.
Consider covering indexes for frequently run, performance-critical queries, so SQLite can skip the table lookup entirely.
Don’t over-index. Every index adds write overhead; only create indexes that correspond to real, recurring query patterns.
Run ANALYZE periodically, especially after significant data changes, so the query planner has accurate statistics to work with.
Verify index usage with EXPLAIN QUERY PLAN rather than assuming an index is being used just because it exists.
Periodically review and drop unused indexes, since schemas and query patterns evolve, and old indexes can quietly outlive their usefulness.

Wrapping Up

Indexes are one of the highest-leverage tools you have for keeping a SQLite database fast as it grows, but “just add an index” is only good advice when it’s backed by an actual understanding of your query patterns. Knowing the difference between a simple single-column index, a composite index, a partial index, and a covering index — and knowing how to verify what SQLite is actually doing with EXPLAIN QUERY PLAN — is what separates guesswork from genuinely effective database tuning. Start with the columns you filter and join on most often, measure before and after with real queries, and resist the urge to index everything just because you can.