TL;DR

Learn how SQL indexes speed up queries. Create indexes, analyze query plans with EXPLAIN, and optimize database performance.

Key concepts

SQL indexes
CREATE INDEX SQL
SQL query performance
database indexing tutorial

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.

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. The database automatically uses it when you filter on the indexed column.

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';

Look for keywords in the output:

SCAN TABLE means a full table scan (slow for large tables)
SEARCH TABLE ... USING INDEX means the index is being used (fast)

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)

But it does NOT help queries filtering only on status, because the index is organized by customer_id first. This is called the leftmost prefix rule.

When to Create Indexes

Indexes aren't free. They speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the database must update the index too. 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)

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';

Practice Exercises

Try these on your own:

Create an index on order_date in the orders table and check the query plan
Create a composite index that speeds up filtering by both status and order_date
Use EXPLAIN QUERY PLAN to compare a query with and without an index
Create a unique index on an email column and test what happens with duplicates
Determine which columns in your most common queries would benefit from indexes

Key Takeaways

Indexes speed up SELECT queries by avoiding full table scans
Use EXPLAIN QUERY PLAN to see how the database executes your query
Composite indexes cover multiple columns — column order matters (leftmost prefix rule)
Indexes slow down INSERT, UPDATE, and DELETE operations
Create indexes on columns used in WHERE, JOIN, and ORDER BY
Unique indexes enforce uniqueness and provide fast lookups
Don't over-index — each index adds storage and write overhead

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.

Two-tier handoff: this document is the complete reading surface. Continue learning for stateful practice, progress, and real sandbox execution.