SQLite Indexes: CREATE INDEX, When to Use Them, and Why

What an Index Actually Is

An index is a separate data structure — a sorted B-tree — that lets SQLite find rows by a column's value without scanning the whole table. Without one, a query like WHERE email = 'rosa@example.com' reads every row and checks each one. With an index on email, SQLite walks the tree in roughly log(n) steps and jumps straight to the match.

That speedup isn't free. The index is a copy of the indexed column plus a pointer back to the row. Every INSERT, UPDATE of an indexed column, and DELETE has to update the index too. Disk space goes up. Write throughput goes down a little. The deal is: pay on writes, save much more on reads.

Creating an Index

The basic syntax:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT,
    created_at TEXT
);

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

Naming convention: most teams use idx_<table>_<column> so it's obvious what the index is for. The name has to be unique across the whole database, not just the table — that's why the table name is part of it.

To remove one:

DROP INDEX idx_users_email;

Indexes are pure performance scaffolding. Dropping one never affects your data — only how fast queries run.

Unique Indexes

A unique index does double duty: it speeds up lookups and enforces that no two rows share the indexed value.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

The third insert fails with UNIQUE constraint failed: accounts.username. SQLite already creates unique indexes automatically for PRIMARY KEY and UNIQUE columns — you'll see them named sqlite_autoindex_<table>_<n>. You only need to write CREATE UNIQUE INDEX when the constraint wasn't declared on the table itself.

What the Planner Actually Does

Adding an index doesn't guarantee SQLite will use it. The query planner picks a strategy per query, and you can see what it picked with EXPLAIN QUERY PLAN:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL,
    status TEXT
);

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

Look for SEARCH ... USING INDEX idx_orders_customer in the output — that means the index is being used. If you see SCAN orders, the planner decided a full table scan was cheaper (often correct on tiny tables) or your query shape prevented it from using the index. There's a whole doc on reading these plans coming up.

When an Index Won't Be Used

Indexes have a few well-known blind spots. Each of these defeats the index on email:

-- Function wraps the column
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Leading wildcard in LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Type mismatch forces a conversion
SELECT * FROM users WHERE email = 12345;

The B-tree is sorted by the raw email value, so anything that transforms the column at query time forces a scan. Fixes vary: store data already-normalized (email_lower column), use an expression index (CREATE INDEX idx ON users(lower(email))), or use SQLite's full-text search for substring matching.

Covering Indexes

If an index contains every column the query needs, SQLite can answer the query without ever touching the table — that's a covering index. The trick is to include extra columns in the index definition:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    type TEXT,
    created_at TEXT
);

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

Because both columns the query asks for live in the index, SQLite reports USING COVERING INDEX. No row fetch needed. Covering indexes are one of the highest-leverage optimizations for hot read paths — the trade-off is a bigger index. Multi-column indexes are their own topic; the next doc covers them properly.

Listing and Inspecting Indexes

Two ways to see what's there:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT UNIQUE,
    name TEXT
);

CREATE INDEX idx_products_name ON products(name);

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

That gives you every index in the database with its CREATE statement. For a single table, PRAGMA index_list('products'); shows just that table's indexes, and PRAGMA index_info('idx_products_name'); shows which columns each one indexes. Anything starting with sqlite_autoindex_ was created automatically for a PRIMARY KEY or UNIQUE constraint — you can't drop those.

When Not to Add an Index

A few situations where adding an index makes things worse:

• Tiny tables. A few hundred rows scan in microseconds. The planner will likely ignore the index anyway, and you've added write overhead for nothing.
• Write-heavy columns rarely queried. Every write updates every index. Indexing a column you almost never filter on is pure cost.
• Low-cardinality columns on their own. An index on a status column with three possible values doesn't narrow much. It can still help as the second column of a composite index, or as a partial index — but on its own, often not worth it.
• Already covered. If you have an index on (a, b), you don't also need one on (a). SQLite uses the leading columns of a composite index for queries that filter on just a.

The honest answer to "should I add this index?" is almost always: try it, run EXPLAIN QUERY PLAN, measure with realistic data, decide.

Next: Composite Indexes

A single-column index covers a lot of ground, but real queries often filter and sort on multiple columns at once. Composite indexes — indexes on (a, b, c) — handle that, and the column order matters more than people expect. That's the next page.