Database Indexing Strategies: Performance Optimization Guide

Previously in this course, we explored analyzing resource bottlenecks to understand where our systems were struggling. Today, we focus on one of the most effective levers for database performance: indexing.

When a database table grows to millions of rows, performing a "full table scan" for every request—where the engine reads every single record to find a match—is the primary cause of high latency. Indexing transforms this $O(N)$ operation into an $O(\log N)$ operation, effectively turning a linear search into a binary-tree search.

Understanding Indexing from First Principles

Think of an index like the index at the back of a textbook. Instead of reading every page to find information on "Normalization," you look it up in the index, find the exact page numbers, and jump straight there.

In a database, an index is a separate data structure (usually a B-Tree) that stores the values of a column alongside pointers to the actual disk blocks where those rows reside. While this makes reading significantly faster, it comes with a trade-off: every INSERT, UPDATE, or DELETE must now also update the index, which adds overhead to write operations.

Analyzing Query Plans

Before you add an index, you must verify the need for one. Every major database engine (PostgreSQL, MySQL, SQLite) provides an EXPLAIN command. This command reveals the "query plan"—the steps the database takes to fetch your data.

Consider a simple users table with 1,000,000 rows. If you run SELECT * FROM users WHERE email = 'test@example.com'; without an index, your query plan will likely show a Seq Scan (Sequential Scan).

SQL
-- Check the current strategy
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

If the plan shows Seq Scan, the database is reading the entire table. If it shows Index Scan or Bitmap Heap Scan, it is using an index.

Strategic Index Application

You should index fields that frequently appear in:

1. WHERE clauses (filtering criteria).
2. JOIN conditions (the columns used to link tables).
3. ORDER BY clauses (to avoid sorting in memory).

Worked Example: Optimizing a Search Query

Let’s say we have an orders table. Our application constantly fetches orders by customer_id to display user profiles.

SQL
-- The slow query
SELECT id, total, created_at FROM orders WHERE customer_id = 550;

-- Step 1: Analyze the plan
EXPLAIN ANALYZE SELECT id, total, created_at FROM orders WHERE customer_id = 550;
-- Result: "Seq Scan on orders  (cost=0.00..25000.00 rows=10 width=24)"

-- Step 2: Create the index
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Step 3: Re-analyze
EXPLAIN ANALYZE SELECT id, total, created_at FROM orders WHERE customer_id = 550;
-- Result: "Index Scan using idx_orders_customer_id on orders..."

By applying this index, we've reduced the search space from a million rows to just the few associated with that specific ID.

Hands-on Exercise

1. Choose a query in your current project's design doc that filters by a non-primary key (e.g., status, created_at, or user_id).
2. Run EXPLAIN ANALYZE on that query in your local development database.
3. Observe the cost and actual time metrics in the output.
4. Create an index on the filtering column.
5. Run the EXPLAIN command again and document the reduction in the cost metric.

Common Pitfalls

• Over-indexing: Adding an index to every column slows down writes and consumes excessive disk space. Only index what you actually query.
• Indexing low-cardinality columns: Indexing a boolean column (e.g., is_active) is rarely useful because the database engine will likely conclude that a full scan is faster than jumping through an index that points to half the table.
• Ignoring Composite Indexes: If you frequently query by two columns together (e.g., customer_id AND status), a single-column index on each is often less efficient than a composite index.

FAQ

Q: Does an index automatically speed up every query? A: No. Indexes are ignored for certain operations, like when using functions on the indexed column (e.g., WHERE LOWER(email) = '...'). You would need a function-based index for that.

Q: How do I know if an index is being used? A: Use the EXPLAIN command. If the query plan doesn't mention your index, the database optimizer has decided it's cheaper to scan the table.

Recap

Effective indexing is the cornerstone of database performance and optimization. By analyzing query plans with EXPLAIN, you ensure that you are only adding indexes that provide measurable gains without bloating your write operations.

Up next: We will cover optimizing network communication by refining how our services exchange data.