Indexes and Performance Basics

As your database grows, query performance becomes critical. This lesson covers the fundamentals of database indexes and how to write efficient SQL.

What is an Index?

An index is a data structure that speeds up data retrieval — like a book's index that lets you jump straight to the right page instead of reading every page.

Without an index, a query must scan every row (full table scan). With an index on the searched column, the database jumps directly to matching rows.

Trade-off: Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must maintain the index). Use them on columns you query frequently.

Creating Indexes

-- Basic index on a single column
CREATE INDEX idx_users_email ON users(email);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_products_sku ON products(sku);

-- Composite index (on multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Partial index (index only a subset of rows)
CREATE INDEX idx_active_products ON products(category)
WHERE stock > 0;

When to Add an Index

Add indexes on columns that are:

• Frequently used in WHERE clauses
• Used in JOIN conditions (ON orders.user_id = users.id)
• Used in ORDER BY on large tables
• Foreign key columns

Primary keys are automatically indexed. UNIQUE constraints create an index automatically.

EXPLAIN — See What the Database Does

EXPLAIN shows the query execution plan without running the query:

-- SQLite
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 1;

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1;

Without an index on user_id:

QUERY PLAN
Seq Scan on orders  (cost=0.00..25.00 rows=10)
  Filter: (user_id = 1)

(Sequential scan = reading every row)

After creating CREATE INDEX idx_orders_user_id ON orders(user_id):

QUERY PLAN
Index Scan using idx_orders_user_id on orders  (cost=0.15..8.17 rows=2)
  Index Cond: (user_id = 1)

(Index scan = jumping straight to matches)

Dropping Indexes

DROP INDEX IF EXISTS idx_users_email;

Query Performance Tips

1. Avoid SELECT *

-- ❌ Retrieves unnecessary columns
SELECT * FROM orders JOIN users ON orders.user_id = users.id;

-- ✅ Only fetch what you need
SELECT o.id, o.quantity, u.name FROM orders o JOIN users u ON o.user_id = u.id;

2. Filter Early

-- ❌ Gets all orders then filters in application code
SELECT * FROM orders;

-- ✅ Filter in the database
SELECT * FROM orders WHERE user_id = 1 AND order_date > '2024-01-01';

3. Avoid Functions on Indexed Columns in WHERE