Back to all posts

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

For developers who know basic SQL and are new to indexes in SQLite.

On this page

An index is a sorted copy of one or more columns that SQLite uses to find matching rows without reading the whole table. For example, an index on the customer_id column of an orders table lets SQLite read only one customer's orders.

You create an index with CREATE INDEX:

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name (column, ...)
[WHERE condition];
  • UNIQUE refuses a second row with the same value in the indexed columns.
  • IF NOT EXISTS makes the statement do nothing when an index with that name exists.
  • index_name is the name that you later pass to DROP INDEX.
  • table_name (column, ...) lists the columns in the order that the index sorts them. A column can also be an expression, such as lower(email).
  • WHERE condition makes a partial index: the index holds only the rows that match.

Create two sample tables in a SQLite database, three customers and their six orders:

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

INSERT INTO customers VALUES
  (1, '[email protected]'),
  (2, '[email protected]'),
  (3, '[email protected]');

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers (customer_id),
  status      TEXT,
  order_date  TEXT
);

INSERT INTO orders VALUES
  (1, 1, 'shipped', '2026-03-01'),
  (2, 2, 'shipped', '2026-03-02'),
  (3, 1, 'open',    '2026-03-04'),
  (4, 3, 'shipped', '2026-03-05'),
  (5, 1, 'open',    '2026-03-07'),
  (6, 2, 'open',    '2026-03-08');

Every statement in this article was run on SQLite 3.50.4, in the order shown. Run them in that order too. The later query plans depend on the indexes that the earlier statements create and drop.

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN shows how SQLite will run a query, without running it. For example, you can check whether SQLite reads the whole orders table to find one customer's orders.

EXPLAIN QUERY PLAN
SELECT order_id, order_date
FROM orders
WHERE customer_id = 1;

Its detail column describes each step of the plan:

detail
SCAN orders

SCAN means that SQLite reads all six orders and keeps the three of customer 1. The sqlite3 shell shows the detail text under a line that reads QUERY PLAN. SQLite EXPLAIN PLAN reads the plans of joins and subqueries line by line.

CREATE INDEX

CREATE INDEX builds an index on one or more columns of a table. For example, you can index the customer_id column of orders, then ask for the same plan again.

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

EXPLAIN QUERY PLAN
SELECT order_id, order_date
FROM orders
WHERE customer_id = 1;
detail
SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)

SEARCH means that SQLite looks up customer 1 in the index and reads only the rows that it finds there. The part in parentheses, customer_id=?, is the WHERE term that the index matched.

Each entry of the index holds a customer_id and the rowid, the unique number under which SQLite stores the row. The entries are sorted by customer_id, so the three entries of customer 1 sit side by side:

The index idx_orders_customer_id keeps each customer_id with the rowid of its row, sorted by customer_id, so SQLite finds the three entries for customer 1 side by side and reads only rows 1, 3 and 5 of the orders table

SQLite uses the same index for a join on customer_id, and for an UPDATE or DELETE that filters on customer_id.

Covering indexes

A covering index holds every column that a query reads, so SQLite answers from the index and never reads the table. For example, a query that returns only the order_id of customer 1's orders is covered by idx_orders_customer_id.

EXPLAIN QUERY PLAN
SELECT order_id
FROM orders
WHERE customer_id = 1;
detail
SEARCH orders USING COVERING INDEX idx_orders_customer_id (customer_id=?)

A column declared INTEGER PRIMARY KEY, such as order_id, is another name for the rowid, and every index entry holds the rowid. So the index holds both columns that this query reads. The CREATE INDEX query above also selected order_date, which only the table holds.

Composite indexes

A composite index sorts by its first column, and by the second column within each value of the first. For example, an index on status and order_date keeps the open orders together, in date order.

CREATE INDEX idx_orders_status_date
ON orders (status, order_date);

EXPLAIN QUERY PLAN
SELECT order_id, order_date
FROM orders
WHERE status = 'open'
ORDER BY order_date;
detail
SEARCH orders USING COVERING INDEX idx_orders_status_date (status=?)

SQLite finds the open orders in one block of the index, already sorted by date, so the plan has no sort step. Without this index, the plan is SCAN orders, then USE TEMP B-TREE FOR ORDER BY: SQLite reads every row, then sorts the open orders in a temporary b-tree. The index also covers the query, because it holds order_date and the rowid.

The column order matters. The dates are sorted only within each status, so a query on order_date alone can't use this index for a SEARCH:

The entries of idx_orders_status_date: the open orders form one block in date order, while the orders from 2026-03-05 are scattered across the open and shipped blocks
EXPLAIN QUERY PLAN
SELECT *
FROM orders
WHERE order_date >= '2026-03-05';
detail
SCAN orders

Put first the column that your queries test with =, and the column that they sort by after it.

CREATE UNIQUE INDEX

A unique index refuses a second row with the same value in its columns. For example, you can stop two customers from registering the same email.

CREATE UNIQUE INDEX idx_customers_email
ON customers (email);

INSERT INTO customers VALUES (4, '[email protected]');

The INSERT fails with:

UNIQUE constraint failed: customers.email

Customer 1 already has [email protected], so SQLite rejects customer 4, and customers keeps its three rows. A unique index accepts any number of NULLs, because SQLite treats every NULL as distinct. So several customers can have no email.

A UNIQUE or PRIMARY KEY constraint in CREATE TABLE makes SQLite create a unique index automatically, with a name such as sqlite_autoindex_customers_1. An INTEGER PRIMARY KEY gets no such index, because it is the rowid. SQLite constraints covers the constraints themselves.

Indexes on expressions

An index on an expression sorts the rows by the result of an expression, such as lower(email). For example, you can find a customer by email whatever the letter case.

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

EXPLAIN QUERY PLAN
SELECT customer_id
FROM customers
WHERE lower(email) = '[email protected]';
detail
SEARCH customers USING COVERING INDEX idx_customers_email_lower (<expr>=?)

In the plan, <expr> stands for the indexed expression, lower(email). SQLite uses the index only when the query contains the expression exactly as it appears in CREATE INDEX. The planner "does not do algebra", as the documentation on indexes on expressions puts it, so WHERE upper(email) = '[email protected]' gets SCAN customers.

PRAGMA index_list

PRAGMA index_list returns one row for each index on a table. For example, you can list the indexes that orders has now.

PRAGMA index_list('orders');
seqnameuniqueoriginpartial
0idx_orders_status_date0c0
1idx_orders_customer_id0c0

Both indexes have origin c, because CREATE INDEX made them. An index made by a UNIQUE constraint has origin u, and one made by a PRIMARY KEY has pk, as the pragma documentation lists.

DROP INDEX

DROP INDEX deletes an index from the database. For example, you can drop idx_orders_status_date and replace it with a smaller, partial index.

DROP INDEX IF EXISTS idx_orders_status_date;

PRAGMA index_list('orders');
seqnameuniqueoriginpartial
0idx_orders_customer_id0c0

Orders now has one index. Drop an index that your queries don't use. SQLite writes to every index of a table on each INSERT and DELETE, and on each UPDATE of an indexed column.

Partial indexes

A partial index holds only the rows that match its WHERE clause. For example, an index on order_date can hold only the open orders, in place of idx_orders_status_date.

CREATE INDEX idx_orders_open_date
ON orders (order_date)
WHERE status = 'open';

EXPLAIN QUERY PLAN
SELECT order_id
FROM orders
WHERE status = 'open' AND order_date >= '2026-03-05';
detail
SEARCH orders USING COVERING INDEX idx_orders_open_date (order_date>?)

The index holds only orders 3, 5 and 6, the open ones. SQLite searches them for dates from 2026-03-05.

SQLite uses a partial index only when the query's WHERE repeats the index's condition, written the same way. Without status = 'open', or with status IN ('open', 'shipped'), the plan goes back to SCAN orders.

What goes wrong

A foreign key without an index

SQLite creates no index for a foreign key. With PRAGMA foreign_keys = ON, SQLite checks that no order points to a customer before it deletes the customer:

PRAGMA foreign_keys = ON;

EXPLAIN QUERY PLAN
DELETE FROM customers
WHERE customer_id = 3;
detail
SEARCH customers USING INTEGER PRIMARY KEY (rowid=?)
SEARCH orders USING COVERING INDEX idx_orders_customer_id (customer_id=?)

The second step checks the orders of customer 3 through idx_orders_customer_id. Without that index, the step is SCAN orders, which the foreign key documentation calls "a linear scan of the entire child table".

Index the child column of every foreign key, the column that holds the reference, such as orders.customer_id. SQLite JOINs covers the joins that these indexes serve.

LIKE and a plain index

LIKE ignores letter case by default, but idx_customers_email compares with case, in the default BINARY collation. So that index can't serve a LIKE:

EXPLAIN QUERY PLAN
SELECT customer_id
FROM customers
WHERE email LIKE 'ana%';
detail
SCAN customers

The LIKE optimization needs an index built with COLLATE NOCASE, and a pattern that doesn't start with a wildcard. After CREATE INDEX idx_customers_email_nocase ON customers (email COLLATE NOCASE), the plan reads SEARCH customers USING COVERING INDEX idx_customers_email_nocase (email>? AND email<?).

Create and manage indexes in DbSchema

DbSchema creates an index from a dialog and writes the CREATE INDEX statement for you. Connect DbSchema to the SQLite file and reverse-engineer its tables onto a diagram, as the SQLite page describes. To add an index to orders:

  1. Double-click the header of the orders table on the diagram.
  2. Open the Primary Key / Unique / Indexes tab and press Add.
  3. Choose Index or Unique Index.
  4. Press Add under Columns and pick a column. Repeat for each column of the index.
  5. Press OK in the index dialog, then OK in the table dialog.
The DbSchema Primary Key / Unique / Indexes tab of a customers table, with the primary key customers_pkey and the unique key customers_email_key

While DbSchema is connected, each OK runs its change in the SQLite database at once. Offline, the change stays in the DbSchema model, and schema synchronization applies it to the database later.

To check a plan, open the SQL Editor, type an EXPLAIN QUERY PLAN statement, and press Run. The steps appear in the result grid under the editor:

The EXPLAIN QUERY PLAN of a query on customer_id in the DbSchema SQL Editor, with the SEARCH step on the orders index in the result grid

Download DbSchema at https://dbschema.com/download.html, connect it to your SQLite file, and run EXPLAIN QUERY PLAN in the SQL Editor on a query that still scans. Connecting, diagrams and the SQL Editor are in the free Community Edition. Schema synchronization and saving the model to a file need Pro.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download