Course contentsShow
← Data Science
Lesson 1078 of 2,14527. Database Design and OptimizationFree lesson

What Are Indexes and Why They Matter

Understand how indexes speed up queries by creating lookup structures, and the trade-offs between read and write performance.

What Are Indexes and Why They Matter

You'll learn: How indexes act as lookup structures that dramatically speed up database queries, and the fundamental trade-off they introduce.

The Core Idea

An index is a separate data structure that the database maintains to help find rows quickly without scanning the entire table. Think of it like a book's index: instead of reading every page to find mentions of "normalization," you check the index at the back, which tells you exactly which pages to visit.

Without an index, if you search for a specific customer ID in a million-row table, the database must check every single row (a full table scan). With an index on the customer ID column, the database jumps directly to the right location—often in just a few lookups.

How Indexes Work

Indexes typically use tree structures (like B-trees) that organize values in a sorted, hierarchical manner. When you query WHERE customer_id = 12345, the database:

  1. Checks the index structure (very fast)
  2. Finds the pointer to the actual row location
  3. Retrieves only that row (or those rows)

This transforms a potentially slow O(n) operation into something closer to O(log n).

The Trade-Off

Here's the catch: indexes speed up reads but slow down writes. Every time you INSERT, UPDATE, or DELETE data, the database must also update all relevant indexes. More indexes mean more maintenance work on every write operation.

If you have a read-heavy workload (like analytics dashboards), indexes are your best friend. In write-heavy scenarios (like real-time transaction processing), too many indexes become expensive overhead.

Key Takeaway: Indexes are lookup structures that dramatically accelerate query performance by avoiding full table scans, but they impose a cost on write operations—the art of database optimization is choosing which columns to index based on your workload patterns.