Understanding Database Indexing: How to Make Your Queries 100x Faster

Database indexing is one of the most impactful performance optimizations available. A well-placed index can turn a 30-second query into a 30-millisecond one. But indexes aren't magic - understanding how they work helps you use them effectively.

How B-Tree Indexes Work

The most common index type. Think of it like a phone book - instead of scanning every page, you jump to the right section.

-- Without index: Full table scan (slow)
SELECT * FROM orders WHERE customer_id = 12345;
-- Scans all 10 million rows!

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

-- With index: Index seek (fast)
SELECT * FROM orders WHERE customer_id = 12345;
-- Jumps directly to matching rows!

Composite Indexes

When queries filter on multiple columns, composite indexes are crucial:

-- This query needs a composite index
SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'shipped'
  AND created_at > '2024-01-01';

-- The index order matters! (left-to-right rule)
CREATE INDEX idx_orders_composite
ON orders(customer_id, status, created_at);

The Leftmost Prefix Rule

A composite index on (A, B, C) can satisfy queries on:

• A alone
• A and B
• A, B, and C
• But NOT B alone or C alone!

When NOT to Index

• Low-cardinality columns (e.g., boolean, status with few values)
• Frequently updated columns - indexes slow down writes
• Small tables - Full scan may be faster than index lookup
• Columns rarely used in WHERE/JOIN/ORDER BY

Analyzing Query Performance

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

-- Look for:
-- "Seq Scan" = Full table scan (bad for large tables)
-- "Index Scan" = Using index (good)
-- "Bitmap Index Scan" = Using index for multiple conditions (good)

Index Types Comparison

Golden Rules of Indexing

1. Always check the query plan with EXPLAIN
2. Index columns used in WHERE, JOIN, and ORDER BY
3. Put high-cardinality columns first in composite indexes
4. Monitor index usage and remove unused indexes
5. Remember: every index slows down INSERT/UPDATE/DELETE