SQLite Indexes: CREATE INDEX, EXPLAIN QUERY PLAN, and Tuning Tips

A query that filters on a column with no index makes SQLite read every row of the table and keep the few that match. An index on that column lets SQLite go straight to the matching rows. You create one with CREATE INDEX, and you check that SQLite uses it by putting EXPLAIN QUERY PLAN in front of the query:

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name (column1, column2, ...)
[WHERE condition];

EXPLAIN QUERY PLAN
SELECT ...;

A plan line that starts with SEARCH means SQLite uses an index to visit only some rows. SCAN means it reads them all.

The examples run in the sqlite3 shell, on SQLite 3.50.4, against two tables:

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    status       TEXT NOT NULL,
    created_at   TEXT NOT NULL,
    total_cents  INTEGER NOT NULL
);

CREATE TABLE customers (
    customer_id  INTEGER PRIMARY KEY,
    email        TEXT
);

What an index is and when it helps

An index does for a table what the index at the back of a book does for its pages. You look a word up in a short sorted list, and the list tells you which pages to open. A SQLite index is a sorted copy of one or more columns, and every entry also holds the rowid of the row it came from.

Because the entries are sorted, the ones for customer_id = 42 sit next to each other, and SQLite reads only those rows from the table. The same sorted order helps these statements:

• equality filters such as WHERE customer_id = 42
• range filters such as WHERE created_at >= '2026-08-01'
• joins such as ON orders.customer_id = customers.customer_id
• sorts such as ORDER BY created_at
• UPDATE and DELETE with a WHERE clause, which find their rows the same way

An index costs something on writes and on disk. Each INSERT and DELETE also writes to the table's indexes, and each index takes space in the database file. So index the columns your queries filter, join or sort by, and leave out the rest, such as total_cents here.

Create an index and check that SQLite uses it

Open the database file in the shell. If the file does not exist yet, the sqlite3 shell creates it, and creating a SQLite database covers the setup.

sqlite3 shop.db

Ask SQLite for its plan before any index exists:

EXPLAIN QUERY PLAN
SELECT order_id, created_at
FROM orders
WHERE customer_id = 42;

QUERY PLAN
`--SCAN orders

Create the index, then run the same EXPLAIN QUERY PLAN again:

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)

The plan now names the index and the WHERE term it matched. These are the lines you will meet most, in the wording of the EXPLAIN QUERY PLAN documentation:

Joins and subqueries, read line by line, are in SQLite EXPLAIN PLAN.

Which columns to index

Start from the queries you run. Take these three:

SELECT order_id, created_at FROM orders WHERE customer_id = 42;

SELECT order_id, created_at FROM orders
WHERE status = 'open'
ORDER BY created_at DESC;

SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

The first one already uses idx_orders_customer_id. The plan for the second has two lines:

QUERY PLAN
|--SCAN orders
`--USE TEMP B-TREE FOR ORDER BY

SQLite reads the whole table, then sorts in a temporary b-tree. An index on the filter column, then the sort column, removes both steps:

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

QUERY PLAN
`--SEARCH orders USING COVERING INDEX idx_orders_status_created_at (status=?)

Inside the index, the open orders are already in created_at order, so the sort is gone, even for DESC. The plan also says covering index: each entry holds status, created_at and the rowid, and order_id is the rowid, because an INTEGER PRIMARY KEY column is another name for it. So SQLite never reads the table, as the query planner overview shows step by step.

Column order in a composite index

The third query filters on two columns, so it gets a composite index:

CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_status (customer_id=? AND status=?)

The entries are sorted by customer_id first and by status only within each customer. A query on customer_id finds one block of entries. A query on status alone finds the open orders spread across the whole index, and on a table with only this index its plan is SCAN orders.

So put first the column that every query on the index filters by. The composite index now also serves WHERE customer_id = 42 alone, which makes idx_orders_customer_id redundant. Drop it to give its write cost back, with IF EXISTS to skip the error if it is already gone:

DROP INDEX IF EXISTS idx_orders_customer_id;

Foreign key columns

SQLite creates no index for a foreign key, and checks foreign keys only after PRAGMA foreign_keys = ON. If customer_id were declared REFERENCES customers (customer_id), deleting a customer would make SQLite look for its orders, and without an index that is "a linear scan of the entire child table", as the foreign key documentation puts it. So index the child column of every foreign key you join or cascade through, as the composite index here already does. SQLite JOINs covers the joins those indexes serve.

SQLite index types

Unique and implicit indexes

A unique index refuses a value that another row already holds:

CREATE UNIQUE INDEX idx_customers_email
ON customers (email);

A second customer with the same email fails:

UNIQUE constraint failed: customers.email

In a unique index, every NULL counts as different from every other NULL, so any number of customers can leave email empty.

A UNIQUE or PRIMARY KEY constraint creates such an index itself. Declared email TEXT UNIQUE, the column would get sqlite_autoindex_customers_1, which the CREATE TABLE documentation calls logically equivalent to the statement above. The exceptions are an INTEGER PRIMARY KEY, which is the rowid itself, and the PRIMARY KEY of a WITHOUT ROWID table. So customer_id has no index:

SELECT name FROM sqlite_schema
WHERE type = 'index' AND tbl_name = 'customers';

SQLite CREATE TABLE and SQLite constraints cover the constraints themselves.

Partial indexes

A partial index holds only the rows that match its WHERE clause. If your queries on created_at only ever ask for open orders, a smaller partial index can replace idx_orders_status_created_at:

DROP INDEX idx_orders_status_created_at;

CREATE INDEX idx_orders_open_created_at
ON orders (created_at)
WHERE status = 'open';

EXPLAIN QUERY PLAN
SELECT order_id FROM orders
WHERE status = 'open' AND created_at >= '2026-08-01';

QUERY PLAN
`--SEARCH orders USING COVERING INDEX idx_orders_open_created_at (created_at>?)

SQLite uses a partial index only when the query's WHERE clause contains the index's condition, written the same way, as the partial index documentation explains. Drop status = 'open' from the query, or write status IN ('open', 'shipped'), and the plan goes back to SCAN orders.

Indexes on expressions

An index can hold the result of an expression instead of a column, which suits a lookup that ignores case:

CREATE INDEX idx_customers_email_lower
ON customers (lower(email));

EXPLAIN QUERY PLAN
SELECT customer_id FROM customers
WHERE lower(email) = '[email protected]';

QUERY PLAN
`--SEARCH customers USING COVERING INDEX idx_customers_email_lower (<expr>=?)

The query has to use the expression exactly as the index wrote it. The indexes on expressions documentation says that the query planner "does not do algebra", so WHERE upper(email) = '[email protected]' gets SCAN customers.

List and inspect the indexes on a table

The .indexes orders dot-command prints the names of the indexes on a table. PRAGMA index_list returns one row per index, with more detail:

PRAGMA index_list('orders');

The pragma documentation defines the columns. unique is 1 for a unique index. origin is c for an index made with CREATE INDEX, u for one made by a UNIQUE constraint, and pk for one made by a PRIMARY KEY constraint. partial is 1 for a partial index.

PRAGMA index_info lists the key columns of one index in order, so you can check a composite index:

PRAGMA index_info('idx_orders_customer_status');

cid is the column's position in the table, counting from 0.

An index with origin u or pk belongs to its constraint, and DROP INDEX refuses it:

index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped

In a setup script, write CREATE INDEX with IF NOT EXISTS, so a second run does nothing instead of stopping at index idx_orders_customer_status already exists.

Create and manage indexes in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

To add an index in DbSchema:

1. In DbSchema, choose Connect to Database, pick SQLite, and point the connection at the database file.
2. Reverse-engineer the schema, so its tables appear in a diagram.
3. Double-click the header of the orders table to open the Table Dialog.
4. In the Indexes tab, add the index, choose Normal, Unique or Primary Key, and pick its columns.

Which file a change reaches depends on the connection. Connected, DbSchema executes it on the live SQLite database at once and logs it in the SQL History pane. Disconnected, it changes only the .dbs design model file. To apply those differences later, open Schema → Synchronize Model with Database: DbSchema lists each difference, indexes included, generates the CREATE INDEX statements, and runs them when you click Execute.

Download DbSchema at https://dbschema.com/download.html, connect to your SQLite file, and run EXPLAIN QUERY PLAN in the SQL Editor on the query that keeps scanning. Connecting, reverse-engineering, the diagrams and the SQL editor are in the free Community Edition, while schema synchronization and saving the model to a file are in Pro.

Sources

1. SQLite documentation: CREATE INDEX
2. SQLite documentation: Partial indexes
3. SQLite documentation: Indexes on expressions
4. SQLite documentation: EXPLAIN QUERY PLAN
5. SQLite documentation: The SQLite query planner
6. SQLite documentation: CREATE TABLE
7. SQLite documentation: SQLite foreign key support
8. SQLite documentation: PRAGMA statements