Indexes and access paths

A table is a pile of receipts. An index is the alphabetical card that says which drawer holds Ada's email. This lesson teaches you to choose those cards for real lookups and joins, prove the engine uses them, and stop adding cards that only slow every INSERT.

A table is receipts; an index is a card catalog

Without an index, “find the account whose email is ada@example.com” means read every row until you match. That is a table scan. It is honest work. It is also how a shop with a million receipts would look up one customer if the clerk had no drawer labels.

An index stores selected column values in order, plus a pointer back to the table row (in SQLite, usually the integer primary key / rowid). The engine can jump near the key, the same way a dictionary jumps to L, then Lovelace. Extra storage. Extra work on every write. Faster chosen reads.

Run the plan with no extra index. Expect a SCAN of accounts. Four rows make the scan cheap; the shape of the plan is what you are learning, not the milliseconds.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  display_name TEXT NOT NULL
);

INSERT INTO accounts (email, display_name) VALUES
  ('ada@example.com', 'Ada'),
  ('grace@example.com', 'Grace'),
  ('linus@example.com', 'Linus'),
  ('margaret@example.com', 'Margaret');

EXPLAIN QUERY PLAN
SELECT account_id, display_name
FROM accounts
WHERE email = 'ada@example.com';

Edit the query, predict the rows it will return, then run it.

CREATE INDEX turns a walk into a jump

CREATE INDEX idx_accounts_email ON accounts(email) builds the catalog. Same SELECT, new access path: SEARCH using that index. The result set is identical. The work is not.

Name indexes from table plus purpose: idx_accounts_email, not index1. You will read these names in plans and in sqlite_master.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  display_name TEXT NOT NULL
);

INSERT INTO accounts (email, display_name) VALUES
  ('ada@example.com', 'Ada'),
  ('grace@example.com', 'Grace'),
  ('linus@example.com', 'Linus'),
  ('margaret@example.com', 'Margaret');

CREATE INDEX idx_accounts_email ON accounts(email);

EXPLAIN QUERY PLAN
SELECT account_id, display_name
FROM accounts
WHERE email = 'ada@example.com';

SELECT account_id, display_name
FROM accounts
WHERE email = 'ada@example.com';

Edit the query, predict the rows it will return, then run it.

PRIMARY KEY is already an access path

You do not CREATE INDEX on account_id when it is INTEGER PRIMARY KEY. SQLite already uses that column as the table's row address. WHERE account_id = 1 is a SEARCH using INTEGER PRIMARY KEY. SELECT * with no WHERE is still a SCAN: you asked for every receipt.

UNIQUE on a column also creates an index. That is why UNIQUE is both a rule and a lookup. A second index on the same key is wasted write cost.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  display_name TEXT NOT NULL
);

INSERT INTO accounts VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace');

EXPLAIN QUERY PLAN
SELECT email, display_name
FROM accounts
WHERE account_id = 1;

EXPLAIN QUERY PLAN
SELECT email, display_name
FROM accounts;

Edit the query, predict the rows it will return, then run it.

UNIQUE indexes enforce and cover

A UNIQUE index rejects a second Ada email. If the SELECT asks only for columns stored in the index, SQLite may use a covering index: answer from the catalog without opening every receipt. SELECT email WHERE email = ... is the textbook case. SELECT * still needs the table for display_name.

Uncomment the duplicate INSERT when you want the UNIQUE error. This runner stops on the first error, so keep that line commented until you have read the covering plan.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  display_name TEXT NOT NULL
);

INSERT INTO accounts (email, display_name) VALUES
  ('ada@example.com', 'Ada'),
  ('grace@example.com', 'Grace');

CREATE UNIQUE INDEX idx_accounts_email ON accounts(email);

EXPLAIN QUERY PLAN
SELECT email
FROM accounts
WHERE email = 'ada@example.com';

SELECT email
FROM accounts
WHERE email = 'ada@example.com';

-- Uncomment to see UNIQUE fail (this runner stops on the first error):
-- INSERT INTO accounts (email, display_name)
-- VALUES ('ada@example.com', 'Ada clone');

Edit the query, predict the rows it will return, then run it.

Composite indexes follow the leftmost prefix

INDEX (last_name, first_name) is one ordered list: Lovelace then Ada, not two independent catalogs. Think last name first in a paper phone book.

• WHERE last_name = 'Lovelace' can use it.
• WHERE last_name = 'Lovelace' AND first_name = 'Ada' can use both columns.
• WHERE first_name = 'Ada' alone cannot: the book is not sorted by first name.
• ORDER BY last_name, first_name can ride the same order. The plan may still say SCAN, but USING COVERING INDEX idx_people_name means walk the catalog in order, not sort a heap of receipts.

Put equality columns that always appear in the filter first. A range (price > 10) usually belongs last: keys after it in the index are hard to use for that query.

CREATE TABLE people (
  person_id INTEGER PRIMARY KEY,
  last_name TEXT NOT NULL,
  first_name TEXT NOT NULL
);

INSERT INTO people (last_name, first_name) VALUES
  ('Lovelace', 'Ada'),
  ('Hopper', 'Grace'),
  ('Torvalds', 'Linus'),
  ('Hamilton', 'Margaret');

CREATE INDEX idx_people_name ON people(last_name, first_name);

EXPLAIN QUERY PLAN
SELECT first_name, last_name
FROM people
WHERE last_name = 'Lovelace';

EXPLAIN QUERY PLAN
SELECT first_name, last_name
FROM people
WHERE last_name = 'Lovelace' AND first_name = 'Ada';

EXPLAIN QUERY PLAN
SELECT first_name, last_name
FROM people
WHERE first_name = 'Ada';

EXPLAIN QUERY PLAN
SELECT first_name, last_name
FROM people
ORDER BY last_name, first_name;

Edit the query, predict the rows it will return, then run it.

LIKE can use an index only when the start is known

email LIKE 'ada%' has a prefix, so in principle the catalog can jump to keys that begin with ada. Two traps still force a SCAN:

• SQLite's default LIKE is case-insensitive for ASCII. A normal (binary) index on email does not match that rule, so the first plan SCANs. PRAGMA case_sensitive_like = ON (or a NOCASE index plus a matching collation) lines the comparison up with the stored keys. Then the same ada% becomes SEARCH.
• LIKE '%example.com' has a leading wildcard. Even with a usable collation, there is no prefix to jump to.

If your app always stores lowercase emails, keep that contract, index the stored column, and prefer = or a prefix range over a fuzzy LIKE.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

INSERT INTO accounts (email) VALUES
  ('ada@example.com'),
  ('ada.lovelace@lab.org'),
  ('grace@example.com'),
  ('linus@kernel.org');

CREATE INDEX idx_accounts_email ON accounts(email);

EXPLAIN QUERY PLAN
SELECT email
FROM accounts
WHERE email LIKE 'ada%';

PRAGMA case_sensitive_like = ON;

EXPLAIN QUERY PLAN
SELECT email
FROM accounts
WHERE email LIKE 'ada%';

EXPLAIN QUERY PLAN
SELECT email
FROM accounts
WHERE email LIKE '%example.com';

Edit the query, predict the rows it will return, then run it.

Wrapping a column hides the index

WHERE lower(email) = 'ada@example.com' computes a new value for every row. The index on email stores the original spelling, including Ada@example.com. The engine cannot jump. You will see SCAN even though an email index exists.

Fixes that keep an access path: store email already normalized, or create an expression index on lower(email) and keep using that same expression in WHERE. Mixing lower(email) in the query with an index on raw email is the classic miss.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

INSERT INTO accounts (email) VALUES
  ('Ada@example.com'),
  ('grace@example.com');

CREATE INDEX idx_accounts_email ON accounts(email);

EXPLAIN QUERY PLAN
SELECT email
FROM accounts
WHERE lower(email) = 'ada@example.com';

CREATE INDEX idx_accounts_email_lower ON accounts(lower(email));

EXPLAIN QUERY PLAN
SELECT email
FROM accounts
WHERE lower(email) = 'ada@example.com';

Edit the query, predict the rows it will return, then run it.

Joins need an index on the search side

A typical nested loop: find the workshop by slug, then find every registration with that workshop_id. workshops.slug is UNIQUE here, so it already has an index. registrations.workshop_id is a foreign key.

SQLite does not automatically index foreign keys. PostgreSQL does not either. MySQL InnoDB usually does. If you only declare REFERENCES and then join from parent to children, expect a SCAN of the child table until you add INDEX (workshop_id).

PRAGMA foreign_keys = ON;

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL
);

CREATE TABLE registrations (
  registration_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL REFERENCES workshops(workshop_id),
  attendee TEXT NOT NULL
);

INSERT INTO workshops (slug, title) VALUES
  ('sql-lab', 'SQL lab'),
  ('indexes', 'Indexes clinic');

INSERT INTO registrations (workshop_id, attendee) VALUES
  (1, 'Ada'),
  (1, 'Grace'),
  (2, 'Linus');

EXPLAIN QUERY PLAN
SELECT r.attendee
FROM workshops AS w
JOIN registrations AS r ON r.workshop_id = w.workshop_id
WHERE w.slug = 'sql-lab';

CREATE INDEX idx_registrations_workshop ON registrations(workshop_id);

EXPLAIN QUERY PLAN
SELECT r.attendee
FROM workshops AS w
JOIN registrations AS r ON r.workshop_id = w.workshop_id
WHERE w.slug = 'sql-lab';

Edit the query, predict the rows it will return, then run it.

Partial indexes keep the catalog small

A partial index stores keys only for rows that match a WHERE on the index itself. Unique active emails: two rows may share an address if at most one is active. Lookups that include is_active = 1 AND email = ... can use that slim index. Lookups of inactive rows will not.

Use partial indexes when a hot query always has the same extra filter (active users, unpaid invoices, current season). Do not invent one for every boolean column.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  is_active INTEGER NOT NULL CHECK (is_active IN (0, 1))
);

INSERT INTO accounts (email, is_active) VALUES
  ('ada@example.com', 1),
  ('old-ada@example.com', 0),
  ('grace@example.com', 1);

CREATE UNIQUE INDEX idx_accounts_active_email
ON accounts(email)
WHERE is_active = 1;

EXPLAIN QUERY PLAN
SELECT account_id
FROM accounts
WHERE is_active = 1 AND email = 'ada@example.com';

INSERT INTO accounts (email, is_active)
VALUES ('ada@example.com', 0);

SELECT account_id, email, is_active
FROM accounts
ORDER BY account_id;

Edit the query, predict the rows it will return, then run it.

Every extra index is paid on INSERT, UPDATE, and DELETE

The table row is not the only write. Each index that includes a changed column is another ordered structure to maintain. sku as PRIMARY KEY is already an index. Adding idx_products_name helps name search. Adding idx_products_stock “because we might filter stock someday” is how catalogs rot.

A scan of three products is faster than maintaining twelve unused indexes. Index the WHERE, JOIN, and ORDER BY you actually ship. After a write, confirm the remaining lookup still SEARCHES.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL
);

CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_stock ON products(stock);

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 12),
  ('PEN-2', 'Gel pen', 40);

UPDATE products
SET name = 'SQL notebook v2',
    stock = stock - 1
WHERE sku = 'NB-1';

SELECT name AS object_name, sql
FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'products'
ORDER BY name;

EXPLAIN QUERY PLAN
SELECT sku
FROM products
WHERE name = 'SQL notebook v2';

Edit the query, predict the rows it will return, then run it.

How to choose an index, as a checklist

1. Write the query first. Copy the real WHERE, JOIN, and ORDER BY. Do not index a column nobody filters.
2. Trust PRIMARY KEY and UNIQUE. Do not duplicate them.
3. Index foreign keys you search or join, unless your engine already did (MySQL InnoDB often yes; SQLite and PostgreSQL no).
4. Prefer one composite that matches a common query over three single-column guesses.
5. Keep expressions and collations identical in the query and the index.
6. Run EXPLAIN QUERY PLAN. If you still see SCAN on a selective lookup, the index is in the wrong shape or the predicate hid the column.
7. Drop indexes that never appear in plans. Writes will thank you.

This runner is tiny. Production plans also depend on row counts. After you load real data, explain again. The next lesson is where those plans get a close reading.

What you should be able to do

• Draw receipts versus a card catalog in one sentence.
• Read SCAN versus SEARCH in SQLite's query plan.
• Leave PRIMARY KEY alone and index emails, slugs, and foreign keys you actually search.
• Use leftmost prefix, prefix LIKE, and matching expressions.
• Refuse an index whose only job is to exist.

Independent lab: catalog slug and roster join

A workshop page loads by slug. The roster lists signups for that workshop. Start with no helpful indexes besides the integer primary keys. Add the two indexes the page needs. Explain the slug lookup and the join. Then explain WHERE lower(slug) = 'sql-lab' and fix that predicate or add an expression index so SEARCH returns.

PRAGMA foreign_keys = ON;

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL,
  title TEXT NOT NULL,
  seats INTEGER NOT NULL
);

CREATE TABLE signups (
  signup_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL REFERENCES workshops(workshop_id),
  attendee TEXT NOT NULL
);

INSERT INTO workshops (slug, title, seats) VALUES
  ('sql-lab', 'SQL lab', 12),
  ('indexes', 'Indexes clinic', 8);

INSERT INTO signups (workshop_id, attendee) VALUES
  (1, 'Ada'),
  (1, 'Grace'),
  (2, 'Linus');

-- 1. Index the lookup the catalog uses.
-- 2. Index the foreign key the roster join uses.
-- 3. Prove both with EXPLAIN QUERY PLAN.
-- 4. Show why lower(slug) still misses the slug index.

SELECT workshop_id, slug, title
FROM workshops
WHERE slug = 'sql-lab';

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Indexing every column after a slow dashboard without reading a plan.
• Creating a second index on the primary key or on a column that is already UNIQUE.
• Indexing first_name alone when every search is last_name then first_name.
• Writing WHERE lower(col) = ... or LIKE '%term%' and blaming the database for a SCAN.
• Declaring FOREIGN KEY and assuming SQLite indexed the child column.
• Keeping unused indexes because dropping them feels risky; unused indexes still tax writes.
• Trusting three-row timings. Plans first, realistic size second.

Lesson review

• I can explain an index as ordered keys plus row pointers, not as a second copy of every column.
• I can tell SCAN from SEARCH in EXPLAIN QUERY PLAN.
• I can leave PRIMARY KEY indexed once and add indexes for real lookups.
• I can use a composite index from the left and say when first-name-only fails.
• I can name function wrapping and leading LIKE wildcards as index hiders.
• I can index a join's search column and accept the write cost of each extra index.