SQLite Indexes

An index is a separate data structure that SQLite maintains alongside a table to speed up data lookups. Without an index, SQLite reads every row in a table to find matches (a full table scan). With an index, it jumps directly to the relevant rows.

The Phone Book Analogy

WITHOUT INDEX (full table scan):
  To find "Smith, John" in an unordered list of 1 million names,
  you read every entry from top to bottom.
  → Slow: O(n) — must check all rows

WITH INDEX (like a sorted phone book):
  Names are sorted A → Z. You open to "S", then "Sm",
  then jump directly to "Smith, John".
  → Fast: O(log n) — skips most of the data

How SQLite Stores an Index

TABLE: employees (1 million rows)
─────────────────────────────────────────
id │ name    │ email              │ salary

INDEX: idx_email  (on email column)
─────────────────────────────────────────
email (sorted)          │ rowid (pointer)
────────────────────────┼────────────────
alice@company.com       │ 1
bob@gmail.com           │ 2
carol@company.com       │ 3
...

Query: WHERE email = 'carol@company.com'
  1. Binary search index → rowid = 3
  2. Jump directly to row 3 in table
  3. Return result instantly

Creating an Index

-- Basic index on one column
CREATE INDEX idx_email ON employees(email);

-- Safe creation
CREATE INDEX IF NOT EXISTS idx_email ON employees(email);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_email_unique ON employees(email);

-- Composite index (multiple columns)
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);

-- Partial index (only indexes rows matching a condition)
CREATE INDEX idx_active ON employees(dept_id) WHERE active = 1;

When SQLite Uses an Index

QUERY USES INDEX:                   INDEX BYPASSED:
──────────────────────────────────  ──────────────────────────────────
WHERE email = 'x@y.com'            WHERE UPPER(email) = 'X@Y.COM'
WHERE salary > 50000               WHERE salary + 0 > 50000
WHERE dept_id = 10 AND salary > 5  WHERE salary > 5 AND dept_id = 10
ORDER BY email (with index)        WHERE email LIKE '%@gmail.com'
                                   (leading wildcard defeats index)

Listing Indexes

-- List all indexes
sqlite> .indexes
idx_dept_salary   idx_email

-- Show indexes on a specific table
PRAGMA index_list(employees);

seq │ name            │ unique │ origin
────┼─────────────────┼────────┼───────
0   │ idx_email       │ 0      │ c
1   │ idx_dept_salary │ 0      │ c

-- See columns in an index
PRAGMA index_info(idx_dept_salary);
seqno │ cid │ name
──────┼─────┼────────
0     │ 2   │ dept_id
1     │ 4   │ salary

Dropping an Index

DROP INDEX IF EXISTS idx_email;

Composite Index Column Order Matters

INDEX: idx_dept_salary ON (dept_id, salary)

USES INDEX:
  WHERE dept_id = 10                  ← leftmost column only ✓
  WHERE dept_id = 10 AND salary > 50  ← both columns ✓

BYPASSES INDEX:
  WHERE salary > 50                   ← skips first column ✗

Rule: a composite index supports queries that start from the
leftmost column and move right. Skipping the first column
defeats the index.

Index Trade-offs

BENEFIT                       COST
──────────────────────────    ──────────────────────────────
Faster SELECT / WHERE         Slightly slower INSERT/UPDATE/DELETE
Faster ORDER BY               Extra disk space
Faster JOIN lookup            Index must be maintained on every write

Index every column? No.
Index columns you frequently filter, sort, or join on.

Automatic Indexes

SQLite automatically creates indexes for PRIMARY KEY and UNIQUE columns. You do not need to create these manually.

CREATE TABLE users (
  id    INTEGER PRIMARY KEY,   ← index created automatically
  email TEXT UNIQUE            ← index created automatically
);

Key Takeaways

Indexes speed up SELECT queries by avoiding full table scans
Create indexes on columns used in WHERE, ORDER BY, or JOIN conditions
Composite indexes work best when queries start from the leftmost column
Indexes slow down writes slightly — only add them where query speed matters
PRIMARY KEY and UNIQUE columns get automatic indexes

Previous lesson

Back to course

Next lesson