Why indexes matter in SQL - Why It Works This Way

Overview - Why indexes matter

What is it?

Indexes are special data structures in databases that help find information quickly. They work like the index in a book, pointing to where data is stored so the database doesn't have to look through everything. Without indexes, searching for data would be slow and inefficient. Indexes make reading data faster but can slow down writing data a bit.

Why it matters

Without indexes, databases would have to scan every row to find what you want, which takes a lot of time especially with large data. This would make websites, apps, and services slow and frustrating to use. Indexes solve this by allowing quick lookups, making everything feel fast and smooth. They are essential for good performance in almost all database systems.

Where it fits

Before learning about indexes, you should understand basic database concepts like tables, rows, and queries. After indexes, you can learn about query optimization, database design, and advanced indexing techniques like composite or full-text indexes.

Mental Model

Core Idea

An index is a shortcut that lets the database find data fast without searching every row.

Think of it like...

Imagine a phone book where names are listed alphabetically with page numbers. Instead of flipping every page, you use the index to jump directly to the right page.

┌─────────────┐       ┌───────────────┐
│   Table     │       │    Index      │
│ (all rows)  │◄──────┤ (sorted keys) │
└─────────────┘       └───────────────┘
       ▲                      ▲
       │                      │
   Full scan             Quick lookup

Build-Up - 7 Steps

1

Foundation

What is an Index in Databases

Concept: Introduce the basic idea of an index as a data structure that speeds up data retrieval.

A database index is like a list that helps the database find rows faster. Instead of checking every row, the database uses the index to jump to the right place. Indexes are usually built on one or more columns of a table.

Result

Queries that use indexed columns run faster because the database looks up data quickly.

Understanding that indexes act as shortcuts helps you see why they speed up searches.

2

Foundation

How Databases Search Without Indexes

Concept: Explain the default method of searching called a full table scan.

Without an index, the database must check every row one by one to find matching data. This is called a full table scan. It works but is slow when tables have many rows.

Result

Searching without indexes takes longer as the table grows bigger.

Knowing the cost of full scans shows why indexes are needed for performance.

3

Intermediate

Types of Indexes and Their Uses

🤔Before reading on: do you think all indexes work the same way or are there different kinds? Commit to your answer.

Concept: Introduce common index types like B-tree and hash indexes and their typical uses.

Most databases use B-tree indexes, which keep data sorted and allow fast range queries. Hash indexes are good for exact matches but not for ranges. Choosing the right index type depends on the query patterns.

Result

Different index types optimize different kinds of queries.

Understanding index types helps you pick the best index for your data and queries.

4

Intermediate

Trade-offs: Indexes Speed Reads but Slow Writes

🤔Before reading on: do you think adding indexes always makes the database faster? Commit to your answer.

Concept: Explain that indexes improve read speed but add overhead to data changes.

When you insert, update, or delete data, the database must also update the indexes. This extra work slows down write operations. So, having too many indexes can hurt performance for writes.

Result

Indexes improve read queries but can slow down data modifications.

Knowing this trade-off helps balance read and write performance in database design.

5

Intermediate

How Indexes Affect Query Plans

Concept: Show how databases decide to use indexes or not when running queries.

The database query planner looks at available indexes and decides whether using an index is faster than scanning the whole table. Sometimes, if the table is small or the query is simple, it skips the index.

Result

Indexes influence how queries are executed and can change performance drastically.

Understanding query plans helps you know when indexes help and when they don't.

6

Advanced

Composite Indexes and Their Impact

🤔Before reading on: do you think an index on multiple columns works the same as separate indexes on each column? Commit to your answer.

Concept: Explain indexes built on multiple columns and how they support complex queries.

Composite indexes store sorted data based on multiple columns together. They are useful when queries filter or sort by several columns at once. However, the order of columns in the index matters for which queries benefit.

Result

Composite indexes speed up multi-column queries but require careful design.

Knowing how composite indexes work helps optimize queries that use multiple filters.

7

Expert

Index Internals and Storage Optimization

🤔Before reading on: do you think indexes store full copies of data or just pointers? Commit to your answer.

Concept: Dive into how indexes store keys and pointers to rows, and how storage affects performance.

Indexes store keys (column values) and pointers to the actual data rows, not full copies of rows. This keeps indexes smaller and faster. Some databases use techniques like clustering or covering indexes to reduce data access further.

Result

Indexes optimize storage and access patterns to speed up queries efficiently.

Understanding index internals reveals why some indexes are faster and how to design them well.

Under the Hood

Indexes are usually implemented as balanced tree structures (like B-trees) that keep keys sorted. Each key points to the location of the full data row. When a query uses an indexed column, the database traverses the tree from root to leaf to find matching keys quickly, avoiding scanning all rows. Updates to data require updating the tree structure to keep it balanced and sorted.

Why designed this way?

The B-tree structure was chosen because it keeps data sorted and balanced, allowing fast search, insert, and delete operations. Alternatives like simple lists or hash tables either don't support range queries well or have poor performance on large datasets. The design balances read speed, write speed, and storage efficiency.

┌─────────────┐
│   Query     │
└─────┬───────┘
      │ Uses indexed column
      ▼
┌─────────────┐
│   B-tree    │
│  (Index)    │
└─────┬───────┘
      │ Points to
      ▼
┌─────────────┐
│   Table     │
│  Data Rows  │
└─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do indexes always make every query faster? Commit to yes or no.

Common Belief: Indexes always speed up all queries.

Reality: Indexes speed up queries that filter or sort on indexed columns but can slow down queries that modify data or queries that don't use those columns.

Why it matters: Adding unnecessary indexes can degrade overall database performance, especially for writes.

Quick: Is an index like a copy of the entire table? Commit to yes or no.

Common Belief: An index stores a full copy of the data for faster access.

Reality: Indexes store only the indexed column values and pointers to the full data rows, not full copies.

Why it matters: Thinking indexes duplicate data leads to overestimating storage needs and misunderstanding performance.

Quick: Can a composite index be used if a query filters only on the second column? Commit to yes or no.

Common Belief: A composite index on (A, B) helps queries filtering only on B.

Reality: Composite indexes work best when queries filter starting from the first column; filtering only on the second column usually doesn't use the index.

Why it matters: Misusing composite indexes can cause unexpected slow queries.

Quick: Do indexes always improve performance regardless of table size? Commit to yes or no.

Common Belief: Indexes help even on very small tables.

Reality: On small tables, full scans can be faster than using indexes due to overhead.

Why it matters: Creating indexes on tiny tables wastes resources without benefit.

Expert Zone

1

Some databases support covering indexes that include all columns needed by a query, avoiding accessing the main table entirely.

2

Index fragmentation over time can degrade performance, requiring maintenance like rebuilding or reorganizing indexes.

3

Partial indexes index only a subset of rows based on a condition, optimizing queries on filtered data.

When NOT to use

Avoid indexes on columns with very low uniqueness (like boolean flags) or on tables with extremely high write rates where index maintenance overhead outweighs read benefits. Instead, consider caching or denormalization strategies.

Production Patterns

In real systems, DBAs monitor query plans and usage statistics to add or drop indexes dynamically. Composite indexes are designed based on query patterns, and index maintenance is scheduled during low-traffic periods to minimize impact.

Connections

Data Structures

Indexes are implemented using tree data structures like B-trees.

Understanding trees in computer science helps grasp how indexes organize and search data efficiently.

Caching

Both indexes and caches aim to speed up data access by avoiding repeated full searches.

Knowing caching principles clarifies why indexes reduce repeated work and improve performance.

Library Cataloging Systems

Indexes in databases function like catalog cards or digital catalogs in libraries that point to book locations.

Recognizing this connection shows how organizing information for quick lookup is a universal problem solved similarly across fields.

Common Pitfalls

#1 Creating indexes on every column without considering query patterns.

Wrong approach:

CREATE INDEX idx_all ON table(column1, column2, column3); -- on all columns indiscriminately

Correct approach:

CREATE INDEX idx_column1 ON table(column1); -- only on columns used in queries

Root cause:

Misunderstanding that indexes have maintenance costs and should be targeted to actual query needs.

#2 Assuming composite indexes work for queries filtering on any column in the index.

Wrong approach:

CREATE INDEX idx_ab ON table(columnA, columnB);
-- expecting this to speed up WHERE columnB = value

Correct approach:

CREATE INDEX idx_b ON table(columnB);
-- separate index needed for filtering on columnB alone

Root cause:

Not knowing that composite indexes are ordered and only support queries starting from the first column.

#3 Ignoring index maintenance leading to fragmentation and slow queries.

Wrong approach:

-- No index maintenance commands run
-- Indexes become fragmented over time

Correct approach:

REINDEX table;
-- or
ALTER INDEX idx_name REBUILD;
-- to maintain index health

Root cause:

Lack of awareness that indexes degrade over time and need upkeep.

Key Takeaways

Indexes are essential shortcuts that let databases find data quickly without scanning every row.

They improve read performance but add overhead to write operations, so balance is key.

Different types of indexes serve different query needs, and composite indexes require careful design.

Understanding how indexes work internally helps optimize database performance and avoid common mistakes.

Proper index use and maintenance are critical for keeping databases fast and efficient in real-world applications.