Indexes & Performance

Speed up queries with smart indexing.

Why Indexes Matter

An index is like a book's index, it helps you find information quickly without reading every page. Without indexes, databases must scan every row to find what you're looking for (called a "full table scan"). With proper indexes, queries that took seconds can run in milliseconds. But indexes aren't free, they use storage and slow down writes. Learning when and how to use them is critical for building fast applications.

The Problem: Slow Queries

Imagine searching for a user by email in a table with 1 million users.

Without Index: Full Table Scan

SELECT * FROM users 
WHERE email = 'alice@example.com';
Expected Output:
Database must:
1. Read row 1, check email ❌
2. Read row 2, check email ❌
3. Read row 3, check email ❌
...
999,999. Read row 999,999, check email ❌
1,000,000. Read row 1,000,000, check email ✓ Found!

Time: ~2-3 seconds (1 million row scans)

With Index: Direct Lookup

Database uses index:
1. Look up 'alice@example.com' in index ✓ Found at row 547,823
2. Read row 547,823 directly

Time: ~1-5 milliseconds (index lookup + 1 row read)
Speed Improvement: 1000x faster with an index! This is why indexes are crucial for production databases.

Creating Indexes

Indexes are created with CREATE INDEX statements.

Basic Index

Index a single column for faster lookups.

CREATE INDEX idx_users_email 
ON users(email);

Unique Index

Ensures no duplicate values (like UNIQUE constraint).

CREATE UNIQUE INDEX idx_users_email_unique 
ON users(email);

Multi-Column Index (Composite)

Index multiple columns together for specific query patterns.

CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, order_date);

Speeds up queries filtering by customer_id, or both customer_id AND order_date

Dropping Indexes

Remove unused indexes to save space and improve write performance.

DROP INDEX idx_users_email;

Automatic Indexes

Some indexes are created automatically by the database.

  • PRIMARY KEY: Always indexed automatically
  • UNIQUE constraints: Create indexes automatically
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,    -- Automatically indexed
    email VARCHAR(255) UNIQUE       -- Automatically indexed
);

No need to manually index these columns

⚠️ NOT Automatically Indexed
  • Foreign keys: In PostgreSQL and Oracle you must index these yourself. (MySQL/InnoDB is the exception: it creates one automatically.)
  • Regular columns: Only indexed if you create them

When to Create Indexes

Not every column needs an index. Index strategically.

✅ Good Candidates for Indexes
  • WHERE clauses: Columns frequently filtered
  • JOIN conditions: Foreign key columns
  • ORDER BY: Columns used for sorting
  • High cardinality: Columns with many unique values

Example: Index WHERE Columns

-- Frequent query
SELECT * FROM orders 
WHERE customer_id = 123;

-- Create index
CREATE INDEX idx_orders_customer 
ON orders(customer_id);

Now lookups by customer_id are fast

Example: Index JOIN Columns

-- Frequent join
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Index the foreign key
CREATE INDEX idx_orders_customer_id 
ON orders(customer_id);

Dramatically speeds up joins

When NOT to Create Indexes

❌ Bad Candidates for Indexes
  • Small tables: Tables with <1000 rows don't benefit
  • Low cardinality AND evenly spread: a boolean that is 50/50 is not worth indexing. A boolean that is 99/1 usually is, via a partial index
  • Frequently updated: Indexes slow down INSERT/UPDATE/DELETE
  • Never queried: Don't index columns you never search

Example: Bad Index (Low Cardinality)

-- Usually pointless: only 2 values, roughly 50/50.
-- Matching half the table is cheaper via sequential scan than via
-- an index plus a random heap fetch for every hit, and the planner
-- knows it, so it will ignore this index anyway.
CREATE INDEX idx_users_is_active ON users(is_active);

-- The exception: heavily SKEWED values. If 99% of rows are
-- status='archived' and you always query the other 1%, a PARTIAL
-- index on just those rows is small, cheap to maintain, and will
-- absolutely be used.
CREATE INDEX idx_users_pending
ON users(created_at)
WHERE status = 'pending';

With an even split the planner skips the first index entirely. Skew is what makes an index on a low-cardinality column worthwhile, and a partial index is how you exploit it.

Composite Indexes: Order Matters

Multi-column indexes have a specific column order that affects which queries can use them.

Index Column Order

CREATE INDEX idx_orders_customer_date_status 
ON orders(customer_id, order_date, status);
This Index Can Speed Up:

✅ WHERE customer_id = ?

✅ WHERE customer_id = ? AND order_date = ?

✅ WHERE customer_id = ? AND order_date = ? AND status = ?

✅ WHERE customer_id = ? AND status = ? (partial use)

❌ WHERE order_date = ? (skips first column)

❌ WHERE status = ? (skips first columns)

Rule: Composite index (A, B, C) works for queries filtering on A, A+B, or A+B+C. It doesn't work for B alone or C alone. Put most selective column first.

Covering Indexes: Maximum Speed

A covering index contains all columns needed by a query, database doesn't need to access the table at all.

Non-Covering Index

CREATE INDEX idx_users_email ON users(email);

SELECT email, name, age FROM users 
WHERE email = 'alice@example.com';
Expected Output:
Process:
1. Use index to find row
2. Read table to get name and age
(2 operations)

Covering Index

-- Works, but name and age become part of the B-tree's sort key
-- even though you never search or sort by them.
CREATE INDEX idx_users_email_name_age
ON users(email, name, age);

-- Better in PostgreSQL: INCLUDE carries the extra columns as payload
-- in the leaf nodes only. Smaller index, and it still enables an
-- index-only scan.
CREATE INDEX idx_users_email_covering
ON users(email) INCLUDE (name, age);

SELECT email, name, age FROM users
WHERE email = 'alice@example.com';
Expected Output:
Process:
1. Read from index only (has all columns)
(1 operation - faster!)

Trade-off: Larger index, but potentially 2x faster queries

Types of Indexes

B-Tree Index (Default)

Most common. Good for equality and range queries.

How a B-Tree Finds a Row (searching for age = 80)

Root50 | 100Leaf20 | 35Leaf60 | 80 | 90Leaf120 | 175< 5050 to 100> 100

Figure 1: The database compares 80 at each level and follows one branch, reaching the row in a handful of hops (O(log n)) instead of scanning every row (O(n)). Leaf nodes point to the actual table rows.

CREATE INDEX idx_users_age ON users(age);

-- A B-tree keeps values in sorted order, so it serves both
-- equality and range predicates, and can supply ORDER BY for free:
--   WHERE age = 25
--   WHERE age > 18
--   WHERE age BETWEEN 20 AND 30
--   ORDER BY age
SELECT count(*) FROM users WHERE age BETWEEN 20 AND 30;

Hash Index

Fast equality lookups only. No range queries or sorting.

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- A hash index stores only hashes, so equality is all it can do:
--   WHERE email = 'alice@example.com'   <- uses the index
--   WHERE email LIKE 'alice%'           <- cannot: no ordering
--   ORDER BY email                      <- cannot: no ordering
-- A B-tree handles all three, which is why it stays the default.
SELECT count(*) FROM users WHERE email = 'alice@example.com';

Partial Index

Index only specific rows (PostgreSQL feature).

CREATE INDEX idx_active_users 
ON users(email) 
WHERE is_active = true;

Smaller index, faster queries for active users only

Full-Text Index

For searching text content.

-- PostgreSQL: a GIN index over a tsvector
CREATE INDEX idx_articles_fts ON articles
USING GIN (to_tsvector('english', title || ' ' || content));

SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content)
      @@ websearch_to_tsquery('english', 'database performance');

-- MySQL: FULLTEXT index + MATCH ... AGAINST
CREATE FULLTEXT INDEX idx_articles_content
ON articles(title, content);

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database performance');

EXPLAIN: Analyzing Query Performance

EXPLAIN shows how the database will execute your query and whether it uses indexes.

Using EXPLAIN

-- EXPLAIN alone shows the plan. EXPLAIN ANALYZE actually runs the
-- query and reports real timings, which is what you usually want.
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'alice@example.com';
Expected Output:
Without an index (real output, PostgreSQL 16, 1,000,000 rows):

 Gather  (cost=1000.00..15554.43 rows=1 width=42)
         (actual time=16.655..18.678 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on users  (cost=0.00..14554.33 rows=1 width=42)
                                   (actual time=13.640..13.640 rows=0 loops=3)
         Filter: (email = 'alice@example.com'::text)
         Rows Removed by Filter: 333333
 Planning Time: 0.160 ms
 Execution Time: 18.688 ms

After CREATE INDEX idx_users_email ON users(email):

 Index Scan using idx_users_email on users
     (cost=0.42..8.44 rows=1 width=42)
     (actual time=0.018..0.019 rows=1 loops=1)
   Index Cond: (email = 'alice@example.com'::text)
 Planning Time: 0.159 ms
 Execution Time: 0.031 ms

18.688 ms -> 0.031 ms, about 600x. Note "Rows Removed by Filter:
333333" in the first plan: each of the 3 parallel workers threw away
a third of the table to find one row.
Look For
  • Seq Scan: reads the whole table. Bad when you expected few rows; perfectly correct when you are reading most of the table anyway
  • Index Scan: Good - using an index
  • High cost numbers: Expensive query
  • High row estimates: Too many rows scanned

Index Maintenance

Rebuild Fragmented Indexes

Over time, indexes become fragmented and slow. Rebuild periodically.

-- PostgreSQL: plain REINDEX takes a lock that blocks writes
REINDEX INDEX idx_users_email;

-- PostgreSQL 12+: rebuild without blocking writes (do this in production)
REINDEX INDEX CONCURRENTLY idx_users_email;

-- MySQL
ALTER TABLE users DROP INDEX idx_users_email;
CREATE INDEX idx_users_email ON users(email);

Monitor Index Usage

Remove unused indexes to save space and improve write speed.

-- PostgreSQL: Find unused indexes
SELECT schemaname, relname, indexrelname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Indexing Best Practices

✅ Always Index Foreign Keys

Foreign key columns are used in JOINs constantly. Always index them.

✅ Index WHERE Clause Columns

Columns frequently used in WHERE conditions should be indexed.

✅ Use Composite Wisely

Put most selective column first in composite indexes. Column order matters!

✅ Don't Over-Index

Every index slows down writes. Only create indexes you actually need.

✅ Test in Production-Like Data

Index performance differs vastly between 100 rows and 1 million rows. Test with realistic data volumes.

✅ Monitor Query Performance

Use EXPLAIN to verify indexes are being used. Measure before and after.

Key Takeaways

  • Indexes dramatically speed up reads - but slow down writes
  • Always index foreign keys - used constantly in JOINs
  • Index WHERE clause columns - columns you filter on frequently
  • Primary keys auto-indexed - don't create duplicate indexes
  • Composite index order matters - most selective column first
  • Don't over-index - every index costs storage and write speed
  • Use EXPLAIN - verify your indexes are actually being used
  • Low cardinality needs skew - an evenly split boolean is a wasted index; a lopsided one deserves a partial index
  • Indexes are the #1 way to improve database performance, learn to use them strategically and your applications will scale effortlessly
Database FundamentalsLesson 8