Mastering Database Indexing: From B-Trees to Specialized Indexes

1. Single-Column Indexes

The most basic type. Index one column for fast lookups.

CREATE INDEX idx_users_email ON users(email);

Best for: Primary key lookups, foreign key joins, unique constraints

2. Composite (Multi-Column) Indexes

Index multiple columns together. Order matters!

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

Best for: WHERE clauses with multiple conditions, ORDER BY optimization

3. Partial Indexes

Index only rows that match a condition. Smaller, faster indexes.

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

Best for: Frequently queried subsets (active users, recent orders)

4. Unique Indexes

Enforce uniqueness and provide fast lookups.

CREATE UNIQUE INDEX idx_unique_email ON users(email);

Best for: Natural keys, business constraints

Full-Text Search Indexes

For searching within text content.

-- PostgreSQL
CREATE INDEX idx_posts_content ON posts USING gin(to_tsvector('english', content));

-- MySQL
ALTER TABLE posts ADD FULLTEXT(content);

Use for: Blog posts, comments, product descriptions

Spatial (GIS) Indexes

For geographic data and location-based queries.

-- PostgreSQL
CREATE INDEX idx_places_location ON places USING gist(location);

-- MySQL
ALTER TABLE places ADD SPATIAL INDEX(location);

Use for: "Find restaurants within 5 miles", mapping applications

Hash Indexes

Fast equality lookups, but no range queries.

CREATE INDEX idx_users_email_hash ON users USING hash(email);

Use for: Simple equality lookups where range queries aren't needed

Covering Indexes

Indexes that include all columns needed for a query, eliminating the need to access the table.

-- Query: SELECT name, email FROM users WHERE status = 'active'
CREATE INDEX idx_users_covering ON users(status, name, email);

-- Index contains all needed data!

Index-Only Scans

When the query can be satisfied entirely from the index without touching the table.

EXPLAIN SELECT count(*) FROM users WHERE created_at > '2025-01-01';
-- "Index Only Scan" - no table access needed!

Partial Indexes with WHERE

Index only the rows you actually query, reducing index size and maintenance.

-- Only index active users (assume 10% of users are active)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Smaller index, faster queries, less maintenance

E-commerce Product Search

-- Query: Products by category, sorted by price
SELECT * FROM products 
WHERE category_id = ? AND active = true 
ORDER BY price ASC LIMIT 20;

-- Optimal index:
CREATE INDEX idx_products_category_price 
ON products(category_id, price) 
WHERE active = true;

Composite index handles WHERE + ORDER BY. Partial index reduces size.

User Timeline

-- Query: Recent posts from followed users
SELECT * FROM posts 
WHERE user_id IN (followed_user_ids) 
ORDER BY created_at DESC LIMIT 50;

-- Index per user for fast timeline generation:
CREATE INDEX idx_posts_user_date 
ON posts(user_id, created_at DESC);

Composite index with descending order matches query pattern perfectly.

Use EXPLAIN plans to understand if indexes are being used effectively.

Track index usage and remove unused indexes regularly.

Load test with production-like data volumes before deploying indexes.

Document why each index exists and what queries it optimizes.