How Database Indexes Work and What They Cost

📅

loading..🐩🐩🐩🐩

Before We Begin 🔗

Today we'll look at the database

index

, an essential tool for working quickly and efficiently with large amounts of data.

You may already know that indexes speed up lookups. But why don't we index everything? Answering that question will help us understand how indexes work and the trade-offs behind them.

What Is an Index? 🔗

Think of the index at the back of a thick book.

Suppose you want to find where “REST API” appears in a book that runs to hundreds of pages. Turning every page from front to back would be inefficient. If you look under “R” in the index, it can point you straight to pages 52, 108, and 210.

A database index serves the same purpose. It's a

lookup structure

built separately from the original data so the database can find specific values more quickly. Without a usable index, it may have to perform a full table scan and examine the data row by row.

Index

Should You Index Everything? 🔗

This is the key question.

Indexing everything is a bad idea.

Imagine listing every word in a book in its index. The index could become much thicker than the book itself. Database indexes have clear benefits and costs, so use them selectively.

Benefits of Indexes 🔗

The main reason to add an index is to

make SELECT queries much faster

. An index on a column used in a WHERE clause or a JOIN condition can let the database locate the relevant rows directly, avoid a full table scan, and respond much faster.

-- 'user_email' 컬럼에 인덱스가 걸려 있다면, 이 쿼리는 매우 빠르게 실행됩니다.
SELECT * FROM users WHERE user_email = 'student@example.com';

Costs of Indexes 🔗

There are two main reasons to avoid indexing everything.

Slower writes (write penalty)
Although SELECT queries can speed up, INSERT, UPDATE, and DELETE operations can slow down. When you add or revise material in a book, you have to update its index too. A database similarly has to add an entry in the right order when it inserts data, and maintain the index when indexed data is updated or deleted. Adding indexes indiscriminately to frequently changed columns can slow down the system as a whole.
Additional storage
An index is a separate data structure that takes up space beyond the original table. The more indexes you build, the more disk space you need.

Indexes involve a

trade-off

between lookup and write performance.

When Should You Use an Index? 🔗

With those trade-offs in mind, consider indexing:

Columns frequently used in WHERE clauses.
Columns frequently used in JOIN conditions. Some database systems automatically index foreign keys.
Columns frequently used for sorting with ORDER BY.
Columns with many distinct values and relatively little duplication.

For example, indexing email addresses, where most values differ, is generally more effective than indexing a gender column with only two values. A column with many distinct values has

high cardinality

Conclusion 🔗

We looked at how database indexes work and the trade-offs they introduce.

An index works like a
book index
and can speed up data retrieval with SELECT.
Indexing everything can slow down writes with INSERT, UPDATE, and DELETE, and requires additional storage.
Decide where an index is worthwhile by considering
how often you read and change the data and how many values are duplicates
.

An interviewer might ask whether you've used indexes, which columns you indexed, and why. Be ready to explain both what you gained from the indexes and what costs you considered.

References 🔗

How Database Indexes Work and What They Cost | PromleeBlog