Index Introduction

Indexes help SQLite find rows faster without scanning the whole table.

Think of an index like a book's table of contents: fast lookup, but extra space and maintenance.

Core Concepts

flowchart LR
  A[Query with filter] --> B{Useful index exists?}
  B -->|Yes| C[Seek matching keys quickly]
  B -->|No| D[Scan many/all rows]
  C --> E[Faster reads]
  D --> F[Slower reads]

Benefit	Cost
Faster SELECT lookups and joins	Extra disk space
Better filtering/sorting performance	Slower INSERT/UPDATE/DELETE

Without Index

• SQLite may scan most or all rows to satisfy a filter.
• Performance degrades as table size grows.

With Index

• SQLite can jump directly to likely matches.
• Huge improvement for selective filters.

Code Examples

-- Setup table commonly filtered by email.
CREATE TABLE users_idx (
  user_id INTEGER PRIMARY KEY,
  email   TEXT NOT NULL,
  name    TEXT NOT NULL
);

INSERT INTO users_idx (email, name)
VALUES
  ('ada@example.com', 'Ada'),
  ('alan@example.com', 'Alan'),
  ('grace@example.com', 'Grace');

Expected output
Table created and sample rows inserted.

-- Create an index for frequent email lookups.
CREATE INDEX idx_users_email
ON users_idx(email);

Expected output
Index idx_users_email created successfully.

-- Query that can benefit from the index.
SELECT user_id, email, name
FROM users_idx
WHERE email = 'alan@example.com';

user_id	email	name
2	alan@example.com	Alan

SQLite-Specific Nuances

SQLite Nuance

SQLite automatically creates indexes for PRIMARY KEY and many UNIQUE constraints.

Add manual indexes for other high-value query patterns.

Common Pitfalls / Best Practices

Pitfall

Indexing every column "just in case," which hurts write performance and bloats storage.

Best Practice

Create indexes based on real query patterns (frequent WHERE, JOIN, and ORDER BY usage).

Quick Challenge

You frequently query users_idx by name. Write the index statement.

View Solution

CREATE INDEX idx_users_name
ON users_idx(name);

Expected output
Index created to speed name-based lookups.