Database Indexing Guide: How to Boost Query Performance 10x

What Is a Database Index?

A database index works like the index at the back of a book. Instead of scanning every page to find a specific word, you consult the index. Similarly, a database index is a data structure that allows you to quickly locate the rows you need without scanning every row in a table.

Without an index, the database performs a Full Table Scan — checking every single row against your filter condition. With a million rows, that could mean a million comparisons. A well-designed index can reduce that to tens or hundreds of comparisons.

How B-Tree Indexes Work

Most relational databases (PostgreSQL, MySQL, Oracle) use a B-Tree (Balanced Tree) as the default index structure.

B-Tree characteristics:

• All leaf nodes are at the same depth (balanced)
• Each node contains sorted keys
• Binary search through left/right subtrees based on key
• O(log n) search time complexity

Even with 10 million rows, a B-Tree is only about 24 levels deep — meaning at most 24 comparisons to find any row.

Basic Index Syntax

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Unique index (prevents duplicates)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

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

-- Drop an index
DROP INDEX idx_users_email;

-- List indexes (PostgreSQL)
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

Composite Indexes and Column Order

A composite index combines multiple columns into a single index. Column order critically affects performance — this is governed by the Leftmost Prefix Rule.

-- Composite index on (user_id, created_at, status)
CREATE INDEX idx_orders_composite ON orders(user_id, created_at, status);

-- Index used (conditions follow left-to-right order)
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2026-01-01';

-- Index NOT used (missing first column condition)
SELECT * FROM orders WHERE created_at > '2026-01-01';
SELECT * FROM orders WHERE status = 'paid';

Cardinality and Selectivity

Cardinality refers to the number of unique values in a column — a key factor in index effectiveness.

For gender = 'M', roughly 50% of rows match. The query optimizer may skip the index and do a full scan instead — which can actually be faster in such cases.

Covering Indexes

A covering index includes all columns needed by a query. The database can return results directly from the index without accessing the main table, dramatically improving performance.

-- Index includes name and email
CREATE INDEX idx_users_covering ON users(id, name, email);

-- This query can be served entirely from the index
SELECT id, name, email FROM users WHERE id = 123;

-- PostgreSQL INCLUDE clause for explicit covering index
CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (total_amount, status);

Verify Index Usage with EXPLAIN

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

-- What to look for:
-- Index Scan       => index being used
-- Seq Scan         => full table scan (no index)
-- Bitmap Heap Scan => index used for many rows

Index Design Principles

1. Index WHERE clause columns: Create indexes on frequently filtered columns
2. Index JOIN columns: Foreign keys should always be indexed
3. Index ORDER BY columns: Columns used frequently for sorting
4. High-cardinality columns first: In composite indexes, put selective columns first
5. Balance with write cost: More indexes means slower INSERT/UPDATE/DELETE

Conclusion

Indexes are a fundamental tool for database performance optimization, but more is not always better. Profile your actual query workload, analyze execution plans, and create indexes strategically where they will deliver the most value.