Indexes & Query Optimization

Indexes make queries faster by allowing the database to locate rows without scanning the entire table.

1. Why Indexes Matter

Without an index:

• The database must scan every row → full table scan

• Slow for large tables (thousands to millions of rows)

With an index:

• The database jumps directly to matching rows (like a book index)

• Speed improves dramatically (10–1000× faster depending on size)

Commonly indexed columns:

• Primary keys

• Foreign keys

• Columns used in WHERE, ORDER BY, JOIN, or GROUP BY

• Frequently searched text fields (e.g., email)

2. Types of Indexes

A. B-tree Index (Most Common)

Used by most relational databases (MySQL, PostgreSQL, SQLite).

Great for:

• Equality lookups (WHERE id = 10)

• Range queries (BETWEEN, <, >)

• Sorting (ORDER BY)

• Joins

Example:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

B. Hash Index

Fast for exact matches, but not for ranges.

Great for:

• WHERE email = 'user@example.com'

Bad for:

• Sorting

• <, >, BETWEEN queries

• Partial matches

PostgreSQL supports hash indexes, but B-trees are usually preferred.

C. Unique Index

Ensures no duplicates and speeds up lookups.

CREATE UNIQUE INDEX idx_users_email ON users(email);

Useful for:

• enforcing unique emails, usernames

• improving lookup performance on those fields

D. Composite Index (Multi-column Index)

Index on multiple columns in order.

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date); 

Rules:

• Can be used left-to-right
  ✔ good for (customer_id)
  ✔ good for (customer_id, order_date)
  ✖ NOT usable for (order_date) alone

Useful for queries like:

SELECT * FROM orders WHERE customer_id = 5 ORDER BY order_date;

3. Using EXPLAIN

EXPLAIN shows how the database plans to run a query.

Example:

EXPLAIN SELECT * FROM orders WHERE customer_id = 5; 

Look for:

This is the core tool for tuning slow queries.

4. Detecting Slow Queries

Signs a query is slow:

• Long execution time

• High CPU / disk usage

• Locks or timeouts

• EXPLAIN shows table scans or large row estimates

Techniques:

• Enable slow query log (MySQL)

• Use pg_stat_statements (PostgreSQL)

• Use APM tools (Datadog, New Relic, Logs)

Common causes:

• Missing indexes

• Indexes on wrong columns

• SELECTing too many columns

• Poor JOIN structure

• Functions on indexed columns (LOWER(email) prevents index use)

5. Improving Performance With Proper Indexing

A. Add indexes on JOIN columns

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

B. Add indexes for WHERE filters

CREATE INDEX idx_products_category ON products(category);

C. Use composite indexes for multi-column filtering

CREATE INDEX idx_logins_user_time ON logins(user_id, login_time);

D. Avoid indexing:

• Columns with low cardinality (e.g., is_active → too many duplicates)

• Very small tables

• Columns frequently updated (indexes slow down writes)

E. Don’t over-index

Too many indexes slow down INSERT, UPDATE, DELETE.

6. Practical Exercises

Exercise 1: Compare Query Speed With / Without Index

Step 1 — Query without index

SELECT * FROM orders WHERE customer_id = 1001;

Check execution time.

Step 2 — Add index

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Step 3 — Run the same query again

Compare:

• Execution time

• EXPLAIN — should now show Index Seek / Index Scan

• Number of rows examined

Exercise 2: Optimize a Slow Report

Scenario:

A report runs slowly:

SELECT c.customer_name, SUM(o.total_amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= '2024-01-01' GROUP BY c.customer_name;

Improve performance:

Add indexes:

CREATE INDEX idx_orders_date ON orders(order_date); CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Re-run EXPLAIN — you should now see:

• Index used on order_date

• Faster join on customer_id