Database Indexing: A Practical Guide to Query Performance
Database Performance Backend

Database Indexing: A Practical Guide to Query Performance

Database performance often comes down to one thing: indexes. A well-indexed database can be thousands of times faster than an unindexed one. But indexes aren't free—they have trade-offs you need to understand.

What Is an Index?

Think of a database index like a book's index. Instead of reading every page to find "TypeScript," you look it up in the index and jump directly to the relevant pages.

Without an index, the database must perform a full table scan, reading every row. With an index, it can jump directly to the relevant rows.

When to Add an Index

Add indexes for columns that appear in:

  1. WHERE clauses
SELECT * FROM users WHERE email = '[email protected]';
-- Index on: email
  1. JOIN conditions
SELECT * FROM posts 
JOIN users ON posts.user_id = users.id;
-- Index on: posts.user_id, users.id
  1. ORDER BY clauses
SELECT * FROM posts ORDER BY created_at DESC;
-- Index on: created_at
  1. Frequently searched columns
SELECT * FROM products WHERE category = 'electronics';
-- Index on: category

Types of Indexes

B-Tree Indexes (Default)

Best for equality and range queries:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at);

Composite Indexes

Index multiple columns together:

-- Good for queries filtering by both status and created_at
CREATE INDEX idx_posts_status_created 
ON posts(status, created_at);

Column order matters! The leftmost columns must be used in queries for the index to be effective.

-- Uses index
SELECT * FROM posts WHERE status = 'published' AND created_at > '2025-01-01';

-- Uses index (leftmost column)
SELECT * FROM posts WHERE status = 'published';

-- DOESN'T use index (no leftmost column)
SELECT * FROM posts WHERE created_at > '2025-01-01';

Unique Indexes

Enforce uniqueness and provide fast lookups:

CREATE UNIQUE INDEX idx_users_email ON users(email);

Partial Indexes

Index only rows that match a condition (PostgreSQL):

-- Only index published posts
CREATE INDEX idx_published_posts 
ON posts(created_at) 
WHERE status = 'published';

Full-Text Indexes

For text search:

-- PostgreSQL
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', content));

-- MySQL
CREATE FULLTEXT INDEX idx_posts_content ON posts(content);

The Cost of Indexes

Indexes aren't free. They:

  1. Take up disk space - Each index is an additional data structure
  2. Slow down writes - Every INSERT, UPDATE, DELETE must update all relevant indexes
  3. Need maintenance - Indexes can become fragmented over time

Index Maintenance

Analyze Query Performance

PostgreSQL:

EXPLAIN ANALYZE 
SELECT * FROM users WHERE email = '[email protected]';

MySQL:

EXPLAIN 
SELECT * FROM users WHERE email = '[email protected]';

Look for:

  • Seq Scan or Table Scan (bad - full table scan)
  • Index Scan or Index Seek (good - using index)

Find Missing Indexes

PostgreSQL:

SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'users';

Remove Unused Indexes

PostgreSQL:

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE '%_pkey';

Delete indexes that are never used:

DROP INDEX idx_unused_index;

Best Practices

1. Index Foreign Keys

Always index foreign key columns:

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);

2. Cover Your Queries

Use covering indexes to avoid table lookups:

-- If you often query: SELECT id, name, email FROM users WHERE status = 'active'
CREATE INDEX idx_users_status_covering ON users(status, id, name, email);

3. Don't Over-Index

More indexes ≠ better performance. Each index:

  • Slows down writes
  • Takes up space
  • Needs maintenance

Only add indexes that provide measurable benefit.

4. Consider Column Selectivity

Indexes work best on columns with high selectivity (many unique values):

  • ✓ Good: email, user_id, order_number
  • ✗ Bad: status (only 2-3 values), boolean fields

5. Monitor Index Bloat

Indexes can become fragmented. Reindex periodically:

-- PostgreSQL
REINDEX TABLE users;

-- MySQL
OPTIMIZE TABLE users;

Common Mistakes

1. Indexing Low-Cardinality Columns

-- Usually not helpful - only 2 possible values
CREATE INDEX idx_users_active ON users(is_active);

2. Wrong Column Order in Composite Index

-- If you mostly filter by status, put it first
-- Good
CREATE INDEX idx_posts ON posts(status, created_at);

-- Less useful
CREATE INDEX idx_posts ON posts(created_at, status);

3. Function Calls in Queries

-- Won't use index on email
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Store lowercase version or use functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Testing Impact

Before deploying new indexes to production:

  1. Test on a copy of production data
  2. Measure query times before and after
  3. Check impact on write performance
  4. Monitor disk usage

The Golden Rule

Index for your reads, but consider your writes.

Start with the queries that matter most to your users and index those. Monitor, measure, and iterate.

Need help optimizing your database? Contact us for a performance audit.