SQLite CREATE INDEX: Unique, Multi-Column, Partial and Expression Indexes

Ninad Pathak

October 7, 2026

A query that filters on an unindexed column makes SQLite read every row in the table, and that gets slower as the table grows. An index lets SQLite jump to the matching rows. In this article I’ll walk you through creating the right one and checking that SQLite uses it.

TLDR

An index lets SQLite find rows by a column’s value without reading the whole table.
Create one with CREATE INDEX index_name ON table(column);.
Check that a query uses it with EXPLAIN QUERY PLAN: SCAN reads every row, and SEARCH … USING INDEX uses the index.

Use CREATE UNIQUE INDEX to forbid duplicates, and put the column you filter with = first in a multi-column index.

What is an index in SQLite?

An index is a separate, sorted structure that maps a column’s values to the rows that hold them. With one, SQLite can jump to the matching rows instead of scanning the table. Without one, a lookup by email has to read every row.

To show you the difference, I will use a users table with 10,000 generated rows:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL, country TEXT, created TEXT, active INTEGER NOT NULL DEFAULT 1);
sqlite> WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 10000) INSERT INTO users (email, country, created, active) SELECT 'user' || i || '@example.com', CASE i % 3 WHEN 0 THEN 'IN' WHEN 1 THEN 'US' ELSE 'DE' END, date('2026-01-01', '+' || (i % 300) || ' days'), i % 10 <> 0 FROM n;
sqlite> SELECT count(*) AS users FROM users;
users
-----
10000

SQLite CREATE INDEX syntax

The full statement, from the CREATE INDEX reference:

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
  ON table_name (column_or_expression [ASC | DESC], ...)
  [WHERE condition];

Part	What it does
`UNIQUE`	Rejects duplicate values in the indexed columns
`IF NOT EXISTS`	Does nothing if an index with that name already exists
`(column, ...)`	One or more columns, or an expression
`WHERE condition`	Makes a partial index that covers only matching rows

How to create an index and check it’s used

Both steps run on the same users table, one statement after another.

Step 1: See the plan before indexing

EXPLAIN QUERY PLAN shows how SQLite will run a query. SCAN means it reads the whole table:

sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE email = 'user42@example.com';
QUERY PLAN
`--SCAN users

Step 2: Create the index and check again

After CREATE INDEX, the same query becomes a SEARCH using the index:

sqlite> CREATE INDEX idx_users_email ON users(email);
sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE email = 'user42@example.com';
QUERY PLAN
`--SEARCH users USING COVERING INDEX idx_users_email (email=?)

COVERING INDEX means the index alone holds every column the query needs (id is stored in every index), so SQLite never reads the table rows.

Examples

Each example below adds a different kind of index to the same users table.

Unique index

“If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed” (CREATE INDEX):

sqlite> CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
sqlite> INSERT INTO users (email) VALUES ('user42@example.com');
Error: stepping, UNIQUE constraint failed: users.email (19)

Multi-column index: column order matters

An index on (country, created) serves queries that filter on country, or on country and created. A query on created alone can’t search it. It can only scan the whole index:

sqlite> CREATE INDEX idx_users_country_created ON users(country, created);
sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE country = 'IN' AND created >= '2026-06-01';
QUERY PLAN
`--SEARCH users USING COVERING INDEX idx_users_country_created (country=? AND created>?)
sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE created >= '2026-06-01';
QUERY PLAN
`--SCAN users USING COVERING INDEX idx_users_country_created

Partial index

“A partial index is an index over a subset of the rows of a table” (Partial Indexes), defined with a WHERE clause. It’s smaller, and SQLite uses it only when the query’s WHERE clause implies the index’s condition:

sqlite> CREATE INDEX idx_users_inactive ON users(created) WHERE active = 0;
sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE active = 0 AND created >= '2026-06-01';
QUERY PLAN
`--SEARCH users USING COVERING INDEX idx_users_inactive (created>?)
sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE created >= '2026-06-01' AND country = 'US';
QUERY PLAN
`--SEARCH users USING COVERING INDEX idx_users_country_created (country=? AND created>?)

The second query doesn’t say active = 0, so it can’t use the partial index, and SQLite picks the (country, created) index instead. Partial indexes have been supported since SQLite 3.8.0.

Unique partial index for soft deletes

Adding UNIQUE to a partial index “provides a mechanism for enforcing uniqueness across some subset of the rows in a table” (Unique Partial Indexes). It fits soft deletes, where an email must be unique among live rows while deleted rows can repeat it:

sqlite> CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT NOT NULL, deleted_at TEXT);
sqlite> CREATE UNIQUE INDEX idx_accounts_live_email ON accounts(email) WHERE deleted_at IS NULL;
sqlite> INSERT INTO accounts (email, deleted_at) VALUES ('ada@example.com', '2026-01-01'), ('ada@example.com', NULL);
sqlite> INSERT INTO accounts (email) VALUES ('ada@example.com');
Error: stepping, UNIQUE constraint failed: accounts.email (19)
sqlite> SELECT id, email, deleted_at FROM accounts;
id  email            deleted_at
--  ---------------  ----------
1   ada@example.com  2026-01-01
2   ada@example.com

The deleted row and one live row share the email. A second live row is rejected.

Index on an expression

An index on a plain column doesn’t help a query that filters on a function of it, such as lower(email). An index on the expression does, but only for the same expression. SQLite uses it when the expression appears “exactly as it is written in the CREATE INDEX statement” (Indexes On Expressions):

sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE lower(email) = 'user42@example.com';
QUERY PLAN
`--SCAN users USING COVERING INDEX idx_users_email_unique
sqlite> CREATE INDEX idx_users_email_lower ON users(lower(email));
sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE lower(email) = 'user42@example.com';
QUERY PLAN
`--SEARCH users USING COVERING INDEX idx_users_email_lower (<expr>=?)

List and drop indexes

After the examples above, the sqlite_schema table lists every index on users. Remove one with DROP INDEX:

sqlite> SELECT name, tbl_name FROM sqlite_schema WHERE type = 'index' ORDER BY name;
name                       tbl_name
-------------------------  --------
idx_users_country_created  users
idx_users_email            users
idx_users_email_lower      users
idx_users_email_unique     users
idx_users_inactive         users

sqlite> DROP INDEX IF EXISTS idx_users_inactive;
sqlite> CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

Edge cases

Watch for these when you add indexes to an existing schema.

Creating an index twice fails without IF NOT EXISTS

A second CREATE INDEX with the same name is an error. Add IF NOT EXISTS to setup scripts that run more than once:

sqlite> CREATE INDEX idx_users_country ON users(country);
sqlite> CREATE INDEX idx_users_country ON users(country);
Error: in prepare, index idx_users_country already exists
sqlite> CREATE INDEX IF NOT EXISTS idx_users_country ON users(country);

Redundant indexes cost writes

After the examples, this table has two indexes on email. The unique one makes the plain one redundant, so in a real schema, keep only idx_users_email_unique. Every index makes inserts and updates do more work and takes disk space. Index only the columns your queries filter or sort by.

Conclusion

Create an index on the columns you search by, then confirm with EXPLAIN QUERY PLAN that the query says SEARCH instead of SCAN. Use UNIQUE to enforce uniqueness and partial indexes to cover only the rows that matter. Keep the index list short, because each one slows down writes.