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

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.

✅ Automatically Indexed

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: You must index these manually!
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: Columns with few unique values (gender, boolean)
Frequently updated: Indexes slow down INSERT/UPDATE/DELETE
Never queried: Don't index columns you never search

Example: Bad Index (Low Cardinality)

-- DON'T index boolean columns
CREATE INDEX idx_users_is_active 
ON users(is_active);  -- Only 2 values: true/false

-- DON'T index gender
CREATE INDEX idx_users_gender 
ON users(gender);  -- Only 2-3 values

Database still scans ~50% of rows, index doesn't help much

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

Process:
1. Use index to find row
2. Read table to get name and age
(2 operations)

Covering Index

CREATE INDEX idx_users_email_name_age 
ON users(email, name, age);

SELECT email, name, age FROM users 
WHERE email = 'alice@example.com';

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.

CREATE INDEX idx_users_age ON users(age);

-- Works great for:
WHERE age = 25
WHERE age > 18
WHERE age BETWEEN 20 AND 30
ORDER BY age

Hash Index

Fast equality lookups only. No range queries or sorting.

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- Works for:
WHERE email = 'alice@example.com'

-- Doesn't work for:
WHERE email LIKE 'alice%'  -- No pattern matching
ORDER BY email             -- No sorting

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.

-- MySQL
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 SELECT * FROM users 
WHERE email = 'alice@example.com';

Without index:
Seq Scan on users  (cost=0.00..18334.00 rows=1)
Filter: (email = 'alice@example.com')
→ Full table scan (slow!)

With index:
Index Scan using idx_users_email on users
  (cost=0.42..8.44 rows=1)
Index Cond: (email = 'alice@example.com')
→ Using index (fast!)

Look For

Seq Scan: Bad - full table scan
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
REINDEX INDEX 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 = bad index - don't index boolean or gender columns
Indexes are the #1 way to improve database performance, learn to use them strategically and your applications will scale effortlessly

Back to "Database Design"

Database FundamentalsLesson 8

Next: "Transactions & ACID"