Indexes and Performance

When your tables contain thousands or millions of rows, the difference between a fast and slow query often comes down to indexes. An index is a data structure that helps the database find rows quickly without scanning the entire table — much like a book's index helps you find a topic without reading every page.

What You'll Learn

By the end of this lesson you will add an index to a table and read the query plan that tells you whether the database actually used it — and, just as often, why it did not. That second half is the work: an index that exists and an index that is used are different things, and the only way to tell them apart is to ask the planner rather than to reason about the schema.

This is what lets the capstone justify its performance notes instead of asserting them: the final project has to show a plan before an index and after it, and say what changed. You arrive here able to filter and sort rows with WHERE and ORDER BY (Filtering and Sorting); what is new is looking underneath a query at the strategy the engine picked to answer it.

A note on scope, because this lesson is about speed and cannot measure it. Every Run here uses a small in-memory database, so nothing you do will be observably faster or slower. What you can observe — exactly and repeatably — is the plan: which strategy the engine chose. Read every claim in this lesson as a claim about plans, not about milliseconds.

How Indexes Work

Without an index, the database performs a full table scan: it checks every single row to find matches. With an index, it can jump directly to the relevant rows.

Creating Your First Index

-- Create a large-ish table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT,
    city TEXT,
    signup_date TEXT
);

-- Insert sample data
INSERT INTO users (id, username, email, city, signup_date)
VALUES
    (1, 'alice', 'alice@example.com', 'New York', '2024-01-15'),
    (2, 'bob', 'bob@example.com', 'London', '2024-02-20'),
    (3, 'carol', 'carol@example.com', 'New York', '2024-03-10'),
    (4, 'david', 'david@example.com', 'Paris', '2024-04-05'),
    (5, 'eva', 'eva@example.com', 'Tokyo', '2024-05-12'),
    (6, 'frank', 'frank@example.com', 'London', '2024-06-18'),
    (7, 'grace', 'grace@example.com', 'New York', '2024-07-22'),
    (8, 'henry', 'henry@example.com', 'Paris', '2024-08-30'),
    (9, 'iris', 'iris@example.com', 'Tokyo', '2024-09-14'),
    (10, 'jack', 'jack@example.com', 'London', '2024-10-01');

-- Create an index on the city column
CREATE INDEX idx_users_city ON users(city);

-- Create an index on email (useful for unique lookups)
CREATE INDEX idx_users_email ON users(email);

-- Query using the indexed column
SELECT username, email, city
FROM users
WHERE city = 'New York';

-- View all indexes on the users table
SELECT name, tbl_name FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

The CREATE INDEX statement builds the index. Whether the database then uses it is a decision the query planner makes per query, and it is easy to write a filter on the indexed column that the planner cannot use. Three common shapes, all measured on this lane against idx_users_city:

Filter	Plan
`WHERE city = 'New York'`	`SEARCH users USING INDEX idx_users_city (city=?)`
`WHERE lower(city) = 'new york'`	`SCAN users`
`WHERE city LIKE 'N%'`	`SCAN users`
`WHERE city != 'London'`	`SCAN users`

The pattern behind the three scans is one idea: an index is sorted by the column's stored value, so it can find a value and it can find a range. Wrap the column in a function and the index is sorted by the wrong thing. Ask for everything except one value and there is no range to seek to — the answer is most of the table anyway. LIKE 'N%' is a genuine prefix range and still scans here, because the default text index cannot serve a LIKE comparison unless the database was configured for it.

So the rule to carry is not "index the column you filter on". It is index the column you filter on, then check the plan.

EXPLAIN QUERY PLAN

SQLite provides EXPLAIN QUERY PLAN to show you how the database executes a query. This is your most important performance debugging tool.

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price REAL,
    in_stock INTEGER
);

INSERT INTO products (id, name, category, price, in_stock)
VALUES
    (1, 'Laptop', 'Electronics', 999.99, 50),
    (2, 'Mouse', 'Electronics', 24.99, 200),
    (3, 'Desk', 'Furniture', 299.99, 30),
    (4, 'Chair', 'Furniture', 199.99, 45),
    (5, 'Keyboard', 'Electronics', 79.99, 150),
    (6, 'Monitor', 'Electronics', 349.99, 75),
    (7, 'Lamp', 'Furniture', 49.99, 100),
    (8, 'Headphones', 'Electronics', 149.99, 80);

-- Check query plan WITHOUT an index
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE category = 'Electronics';

-- Create an index on category
CREATE INDEX idx_products_category ON products(category);

-- Check query plan WITH an index
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE category = 'Electronics';

-- The actual query still works the same
SELECT name, price FROM products WHERE category = 'Electronics';

The plan comes back as a small table whose detail column holds the answer. Read that column and ignore the rest:

SCAN products means a full table scan — every row is examined.
SEARCH products USING INDEX idx_products_category (category=?) means the index is doing the finding. The part in parentheses tells you which columns of the index the query was able to use.
SCAN products USING COVERING INDEX idx_x is a third answer you will meet below: still a scan, but of the index rather than the table, because the index happened to contain every column the query asked for.

Note the exact wording, because older documentation and many tutorials quote a form that also carries the word TABLE after the verb. This version of SQLite does not print that word — the verb is followed directly by the table name, so SCAN products is what you are looking for.

Predict

An index is built on the city column, and then two queries filter on that same column — one with an equality test and one wrapping the column in lower(). What does the detail column of the two query plans say?

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT NOT NULL, city TEXT NOT NULL);
INSERT INTO users VALUES (1,'alice','New York'),(2,'bob','London'),(3,'carol','New York');
CREATE INDEX idx_users_city ON users(city);
EXPLAIN QUERY PLAN SELECT username FROM users WHERE city = 'New York';
EXPLAIN QUERY PLAN SELECT username FROM users WHERE lower(city) = 'new york';

Both report SEARCH using the index, because both filter on an indexed column.
The first reports SEARCH using the index; the second reports SCAN.
The second query fails, because a function is not allowed in a WHERE clause.

Watching the plan change

An index changes a plan the moment it exists, and a plan taken before it is the only way to prove that. Below the fence, put the two EXPLAINs around the CREATE INDEX and read them as a before and after.

CREATE TABLE readings (id INTEGER PRIMARY KEY, sensor TEXT NOT NULL, value INTEGER NOT NULL);
INSERT INTO readings VALUES (1,'a',10),(2,'b',20),(3,'a',30);

Arrange the code

These four lines demonstrate that an index changed a query plan: two identical EXPLAIN QUERY PLAN statements with a CREATE INDEX between them, plus the query itself. Put them in the order that proves the point — then answer what the order is really testing: why does the demonstration need two plans rather than one, and what would the output show if the index were created before both of them?

EXPLAIN QUERY PLAN SELECT id FROM readings WHERE sensor = 'a';
CREATE INDEX idx_sensor ON readings(sensor);
SELECT id, value FROM readings WHERE sensor = 'a' ORDER BY id;
EXPLAIN QUERY PLAN SELECT value FROM readings WHERE sensor = 'a';

Composite Indexes

A composite index covers multiple columns. The order of columns matters.

-- Create an orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT,
    total REAL,
    order_date TEXT
);

INSERT INTO orders (id, customer_id, status, total, order_date)
VALUES
    (1, 101, 'completed', 150.00, '2025-01-05'),
    (2, 102, 'pending', 89.99, '2025-01-06'),
    (3, 101, 'completed', 250.00, '2025-01-10'),
    (4, 103, 'shipped', 175.00, '2025-01-12'),
    (5, 102, 'completed', 320.00, '2025-01-15'),
    (6, 101, 'pending', 45.00, '2025-01-18'),
    (7, 103, 'completed', 99.99, '2025-01-20'),
    (8, 104, 'shipped', 560.00, '2025-01-22');

-- Create a composite index on customer_id and status
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- This query can use the full composite index
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE customer_id = 101 AND status = 'completed';

-- This query can use the index (leftmost prefix)
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE customer_id = 101;

-- This query CANNOT efficiently use the composite index
-- because it skips the first column
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE status = 'completed';

-- Run the actual queries
SELECT id, total, order_date FROM orders
WHERE customer_id = 101 AND status = 'completed';

A composite index on (customer_id, status) works for queries filtering on:

customer_id alone (leftmost prefix)
customer_id AND status (full index)

A query filtering only on status cannot seek into that index, because the index is sorted by customer_id first and the status values are scattered through it. This is called the leftmost prefix rule, and the plan for WHERE status = 'completed' above confirms it: SCAN orders.

There is an exception worth knowing, because it looks like a contradiction when you first meet it. Ask for only the columns the index itself contains, and the planner will read the index instead of the table:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, total REAL);
INSERT INTO orders VALUES (1,101,'completed',150.0),(2,102,'pending',89.99),(3,101,'completed',250.0);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Asks for total, which is not in the index
EXPLAIN QUERY PLAN SELECT total FROM orders WHERE status = 'completed';

-- Asks only for columns the index holds
EXPLAIN QUERY PLAN SELECT customer_id, status FROM orders WHERE status = 'completed';

The first reports SCAN orders. The second reports SCAN orders USING COVERING INDEX idx_orders_customer_status — still a scan, because the leftmost prefix rule still applies and there is no seek to make, but a scan of the index rather than of the table. The index is covering for that query: every column asked for is in it, so the table itself never has to be opened. So the honest statement of the rule is that a composite index does not let a status-only query seek, though it may still serve it as a covering scan.

When to Create Indexes

Indexes aren't free. Each one is a second structure the database has to keep in step, so every INSERT, UPDATE and DELETE on the table does more work, and each index occupies storage. That trade-off is real on a disk-backed database and is the standard reason not to index everything — you cannot observe it on this in-memory lane, so take it as the received engineering rule it is rather than as something this lesson demonstrates. Here's how to decide.

Good Candidates for Indexes

-- Create a table to demonstrate index use cases
CREATE TABLE transactions (
    id INTEGER PRIMARY KEY,
    account_id INTEGER,
    type TEXT,
    amount REAL,
    transaction_date TEXT,
    description TEXT
);

INSERT INTO transactions (id, account_id, type, amount, transaction_date, description)
VALUES
    (1, 1001, 'credit', 5000.00, '2025-01-01', 'Salary deposit'),
    (2, 1001, 'debit', 50.00, '2025-01-02', 'Coffee shop'),
    (3, 1002, 'credit', 3200.00, '2025-01-01', 'Salary deposit'),
    (4, 1001, 'debit', 120.00, '2025-01-05', 'Grocery store'),
    (5, 1003, 'credit', 4500.00, '2025-01-01', 'Salary deposit'),
    (6, 1002, 'debit', 89.99, '2025-01-03', 'Online shopping'),
    (7, 1001, 'debit', 200.00, '2025-01-08', 'Electric bill'),
    (8, 1003, 'debit', 35.00, '2025-01-04', 'Gas station');

-- Index on columns used in WHERE clauses
CREATE INDEX idx_trans_account ON transactions(account_id);

-- Index on columns used in JOIN conditions
-- (account_id would be the join column)

-- Index on columns used in ORDER BY
CREATE INDEX idx_trans_date ON transactions(transaction_date);

-- Composite index for common query patterns
CREATE INDEX idx_trans_account_type ON transactions(account_id, type);

-- Query that benefits from indexes
SELECT type, amount, transaction_date, description
FROM transactions
WHERE account_id = 1001 AND type = 'debit'
ORDER BY transaction_date;

-- Check what indexes exist
SELECT name FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'transactions';

Index Trade-offs

Create indexes on columns that are:

Frequently used in WHERE clauses
Used in JOIN conditions
Used in ORDER BY clauses
Have high selectivity (many distinct values)

Avoid indexes on columns that are:

Rarely queried
Updated very frequently
Have low selectivity (e.g., a boolean column with only TRUE/FALSE)

Debug

Two of these four users live in New York, but the city column was entered inconsistently: one row says New York and another says new york. This query is meant to find both of them and it returns only one. Commit a hypothesis about what the comparison is actually comparing, then fix both the query and the index so both users come back and the plan still reports a SEARCH rather than a SCAN.

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT NOT NULL, city TEXT NOT NULL);
INSERT INTO users VALUES (1,'alice','New York'),(2,'bob','London'),(3,'carol','new york'),(4,'dave','Paris');
CREATE INDEX idx_users_city ON users(city);
SELECT username FROM users WHERE city = 'new york' ORDER BY username;

Expected output:

4 row(s) affected
username
--------
alice
carol

Before you change anything — why do you think this fails?

Transfer

You have been reading SQLite plans that say SCAN orders and SEARCH orders USING INDEX idx_x. A colleague sends you a PostgreSQL plan for the same kind of query, and it says Seq Scan on orders followed by Index Scan using idx_x on orders. Which statement describes what actually transfers between the two?

Nothing transfers. Each engine has its own planner, so a plan learned on one says nothing about the other.
The distinction transfers: both name a whole-table read and an index-assisted read, and the diagnostic question is the same in both.
The wording transfers, so you can grep PostgreSQL plans for the word SCAN and read them the same way.

Recall

No notes. In 07-subqueries you met a query that returned no rows because of a NULL. If a subquery's result column contains a NULL and you use it with NOT IN, what does the outer query return?

Every row, because an unknown value cannot exclude anything.
No rows at all, silently and with exit code 0.
The correct rows, because SQL ignores NULLs inside an IN list.

Unique Indexes

A unique index enforces that no two rows can have the same value in the indexed column. This is both a performance feature and a data integrity constraint.

-- Create accounts table with unique constraints
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);

-- Create unique indexes
CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);
CREATE UNIQUE INDEX idx_accounts_email ON accounts(email);

-- These inserts work fine
INSERT INTO accounts (id, username, email)
VALUES
    (1, 'alice', 'alice@example.com'),
    (2, 'bob', 'bob@example.com');

-- View the data
SELECT * FROM accounts;

-- This would fail because 'alice' username already exists:
-- INSERT INTO accounts (id, username, email)
-- VALUES (3, 'alice', 'different@example.com');

-- Verify indexes exist
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'accounts';

Try It Yourself

Index a real filter, and make the query and the index agree with each other. The starter runs and reports one debit for account 101, but the account has two — one of them was recorded with a capital D.

Build

Account 101 has two debits, ids 1 and 4, but one of them was entered as Debit rather than debit. The starter finds only the lowercase one and the table carries no index at all. Work TODO 1 and TODO 2 so both debits are reported and the events table carries exactly one index that the filter can actually use.

CREATE TABLE events (id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL, kind TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO events VALUES (1,101,'Debit',5000),(2,101,'credit',12000),(3,102,'debit',800),(4,101,'debit',250),(5,103,'credit',9000);
-- TODO 1: the kind column is inconsistently capitalised, so an equality test
--         against 'debit' misses id 1. Normalise both sides of that comparison
--         so ids 1 and 4 both come back.
-- TODO 2: add one index named idx_events_account_kind that this filter can
--         seek into. It must cover account_id and the same normalised form of
--         kind that TODO 1 compares against, in that order. The final count
--         must report exactly 1 index.
SELECT id, amount_cents FROM events WHERE account_id = 101 AND kind = 'debit' ORDER BY id;
SELECT COUNT(*) AS indexes_on_events FROM sqlite_master WHERE type = 'index' AND tbl_name = 'events';

Expected output:

5 row(s) affected
id | amount_cents
-----------------
1 | 5000
4 | 250
indexes_on_events
-----------------
1

Before you run — what's your plan for the first TODO?

Once it passes, try two variations and predict each before you run it.

Run EXPLAIN QUERY PLAN on your final SELECT, first with the CREATE INDEX line deleted and then with it restored. Without the index the plan reads SCAN events; with it, SEARCH events USING INDEX idx_events_account_kind (account_id=? AND <expr>=?). That pair is the before-and-after the capstone's performance notes are made of.
Swap the index's two columns, so it reads (lower(kind), account_id), and take the plan again. It still reports a SEARCH, because this query constrains both columns and either order can serve it. The column order only starts to matter when a query constrains just one of them — which is the leftmost prefix rule, and why an index built for two queries is usually built around the one that filters on less.

Key Takeaways

Indexes let a query find rows without examining every one — but only when the planner can use them, which is a per-query decision
Use EXPLAIN QUERY PLAN and read the detail column: SCAN means every row, SEARCH USING INDEX means the index did the finding
This SQLite prints the verb followed directly by the table name, as in SCAN products — older tutorials quote an obsolete form carrying an extra word after the verb
A function around a column moves the question off the index; index the same expression the query asks about
Composite indexes follow the leftmost prefix rule, so a filter on the second column alone cannot seek — though it can still get a covering scan if the index holds every column the query asks for
Each index is another structure to maintain on every write, which is the standard reason not to index everything
Unique indexes enforce uniqueness and provide fast lookups

Pro Tip: Start without indexes and add them when you notice slow queries. Use EXPLAIN QUERY PLAN to verify that your index is actually being used. An unused index wastes space and slows writes for no benefit.

Next Steps

Now that you understand how the database finds data efficiently, the next challenge is keeping your complex queries readable. The next lesson covers views and Common Table Expressions (CTEs) — two tools that let you name and reuse query logic. Views save a query permanently in the database so any user can reference it like a table, while CTEs break a long query into named steps that read from top to bottom instead of inside-out.