Teaching Database Indexes to a Junior Backend Developer

Understanding Database Indexes: A Practical Guide

What an index is

A database index is a separate data structure the database maintains alongside a table. It stores the values of one or more columns in a sorted, searchable form, together with pointers back to the actual rows. The table itself stores your data; the index is an extra structure whose only job is to help the database find rows faster.

Here is one analogy, and it is the only one you need: an index is like the index at the back of a textbook. If you want every page that mentions "transactions," you do not read the whole book page by page; you look up "transactions" in the alphabetically sorted index, get a short list of page numbers, and jump straight to them. Without that index, your only option is to scan every page. A database faces exactly the same choice: use an index to jump to matching rows, or scan the whole table.

Now the real behavior, without the analogy. When you run a query like SELECT * FROM orders WHERE customer_id = 42, the database has two basic strategies. A full table scan reads every row and checks the condition, which costs time proportional to the size of the table. An index lookup instead searches the sorted index structure for customer_id = 42, finds the matching entries quickly, and follows the stored pointers to fetch just those rows. For a large table where only a few rows match, the index path can be thousands of times cheaper.

How a B-tree index works, at a high level

The most common index type is a B-tree. It is a balanced tree structure where keys are kept in sorted order. The top node divides the key space into ranges, each child node subdivides further, and the bottom level (the leaves) contains the actual indexed values with pointers to the table rows. Because the tree is balanced and each node holds many keys, even a table with hundreds of millions of rows usually needs only three to five node reads to find any specific value.

Because a B-tree keeps values in sorted order, it supports more than exact matches. It efficiently handles range conditions (WHERE created_at >= '2024-01-01'), prefix matches on strings (WHERE email LIKE 'anna%'), and can return rows already sorted, which lets the database skip a separate sort step for matching ORDER BY clauses.

Why indexes cost something

Indexes are not free, and this is the trade-off you must internalize.

Writes get slower. Every INSERT must add an entry to every index on the table. Every DELETE must remove entries. Every UPDATE that changes an indexed column must update the corresponding index entries. A table with six indexes effectively does up to seven writes for every logical row insert. On write-heavy tables, careless indexing measurably hurts throughput.

Storage grows. Each index is a full copy of the indexed column values plus pointers and tree structure. Indexes on large tables can rival or exceed the size of the table itself, which also affects backups and memory caching.

So the guiding principle is: indexes trade write cost and storage for read speed. You add them where reads benefit clearly, not everywhere.

Selectivity: the key concept for deciding value

Selectivity describes how well a condition narrows down rows. A highly selective column has many distinct values relative to the row count. An email or order ID is highly selective: filtering on it returns one or a handful of rows out of millions, and an index shines. A column like status with three values ('pending', 'shipped', 'cancelled') or a boolean is_active flag has low selectivity: filtering may still match 40% of the table.

Why does this matter? If a condition matches a large fraction of the table, jumping back and forth between the index and the table for millions of rows is often slower than just scanning the table sequentially. Query planners know this and will ignore an index when the estimated match fraction is too high. As a rough intuition, if a typical query using the index would return more than a few percent of the rows, the index may not be used at all, and it is pure overhead.

Composite indexes and the leftmost-prefix rule

An index can cover multiple columns, in a specific order. For example:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

Think of this as sorting entries first by customer_id, then by created_at within each customer, like a phone book sorted by last name, then first name.

The leftmost-prefix idea follows directly from that sort order. This index can efficiently serve:

• WHERE customer_id = 42
• WHERE customer_id = 42 AND created_at >= '2024-01-01'

But it cannot efficiently serve WHERE created_at >= '2024-01-01' alone, because entries for a given date range are scattered across all customers; you cannot use a phone book sorted by last name to find everyone named "Anna." The index is usable only when your conditions constrain a prefix of its column list, starting from the leftmost column. This means (customer_id, created_at) and (created_at, customer_id) are different indexes serving different queries, and column order should follow your most important query patterns. A common rule of thumb: put equality-filtered columns first, then the range or sort column.

When an index does not help

• Low selectivity: filtering WHERE is_active = true on a table where 90% of rows are active. The planner will scan instead.
• Functions or expressions on the column: WHERE LOWER(email) = 'x@y.com' cannot use a plain index on email, because the index stores raw values, not transformed ones. (Some databases support expression indexes, but the plain index will not be used.)
• Leading wildcards: WHERE name LIKE '%son' cannot use a B-tree, because the sorted order helps only when the prefix is known.
• Skipping the leftmost column of a composite index, as described above.
• Tiny tables: for a few hundred rows, a scan is already fast; the index adds write cost for no benefit.
• Type mismatches or implicit casts on the indexed column can also prevent index use.

Two small examples

A useful index. Suppose your application constantly runs:

SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Then this index fits perfectly:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

The database jumps to customer 42's entries, which are already sorted by created_at, reads the newest 20, and stops. It is fast on a table of any size, and it also serves plain lookups by customer_id thanks to the leftmost prefix.

A problematic index. Suppose you instead create:

CREATE INDEX idx_orders_status ON orders (status);

where status has three possible values and most rows are 'shipped'. A query like SELECT * FROM orders WHERE status = 'shipped' matches most of the table, so the planner will do a table scan anyway. The index is rarely or never used, yet every insert and every status update pays to maintain it. This is a net loss. (An exception worth knowing: indexing a low-cardinality column can pay off when one value is rare and heavily queried, such as the small set of 'pending' orders, but the generic version above is a classic mistake.)

Practical checklist before adding an index

1. Identify the actual slow query first; do not index speculatively. Look at real query patterns and use EXPLAIN to see the current plan.
2. Check selectivity: will typical queries using this index return a small fraction of the table? If not, reconsider.
3. For multi-column filters and sorts, design one composite index with the right column order (equality columns first, then range/sort columns) rather than several single-column indexes.
4. Confirm the leftmost-prefix rule: does your most common query constrain the first column of the index?
5. Make sure the query does not defeat the index with functions, leading wildcards, or type casts on the indexed column.
6. Weigh write traffic: on heavily written tables, every extra index has a real cost. Remove indexes that overlap with or are prefixes of others.
7. Check whether an existing index already covers the query before creating a new one.
8. After creating the index, verify with EXPLAIN that the planner actually uses it, and measure the query time before and after.
9. Periodically review and drop unused indexes; they cost writes and storage forever.

The core mental model to keep: an index is a sorted lookup structure you pay for on every write so that specific, selective reads become cheap. Add one when you can name the query it serves and show that it helps.

Let's talk about database indexes. You've been writing SQL queries, and they probably work great on your development machine with a few hundred rows. But when a table grows to millions of rows, some of those queries can become incredibly slow. An index is the most common tool we use to solve this problem.

What is a Database Index? An Analogy

Think of an index like the index at the back of a textbook. If you want to find every page that mentions "B-trees," you don't read the entire book from cover to cover. Instead, you go to the index, find the entry for "B-trees," and see a sorted list of page numbers (e.g., 112, 254, 256). This is much faster.

A database index works the same way. It's a separate data structure that the database maintains. It holds a copy of the data from one or more columns in a sorted order, along with a pointer back to the original row in the table. When you query for data in that column, the database can use this sorted index to quickly find the pointers to the rows you need, instead of scanning the entire table one row at a time (a "full table scan").

How It Works: The B-Tree

Most relational databases use a B-tree for their standard indexes. You don't need to know the deep computer science details, but at a high level, a B-tree is a self-balancing tree structure. Imagine a branching hierarchy. To find a value, the database starts at the root node and follows a path down the branches. Each step dramatically narrows down the search. This allows the database to find any value in a massive dataset with very few steps, making searches incredibly efficient (logarithmic time complexity, or O(log n)) compared to a full table scan (linear time complexity, or O(n)).

The Trade-offs: Write Speed and Storage

Indexes are not free. They come with two main costs:

1. Storage: An index is a physical data structure stored on disk. It duplicates data from your table, so it consumes extra storage space.
2. Slower Writes: This is the most important trade-off. When you INSERT, UPDATE, or DELETE a row, the database doesn't just change the table. It must also update every single index that contains the affected columns to keep it in sync and sorted. If you have a table with five indexes, one INSERT statement actually results in six writes: one for the table and one for each index. This can significantly slow down write-heavy applications.

Key Concepts for Practical Indexing

1. Selectivity
Selectivity refers to how many unique values are in a column relative to the total number of rows.

• High Selectivity: A column where most values are unique, like user_id or email_address. Indexes on these columns are very effective because they can narrow a search down to just one or a few rows very quickly.
• Low Selectivity: A column with very few unique values, like a boolean is_active flag (true/false) or a status column ('pending', 'active', 'inactive'). An index on a low-selectivity column is often useless. If you search for all active users, that might be 80% of your table. It's faster for the database to just read the whole table than to jump back and forth between an index and the table data.

2. Composite Indexes and the Leftmost-Prefix Rule
A composite index is an index on two or more columns, like CREATE INDEX idx_name ON users (last_name, first_name);.

The order of columns is critical. This is the leftmost-prefix rule. An index on (A, B, C) can be used by the database to speed up queries that filter on:

• A
• A and B
• A, B, and C

However, it cannot be used for queries that only filter on B, C, or B and C. The database needs the first column(s) in the index definition to start its search.

SQL Examples

Let's assume a products table with millions of rows.

Good Index Example:
Many users are searching for products by a unique product code.

-- The Query
SELECT name, price FROM products WHERE product_code = 'ABC-12345';

-- The Solution: An index on a high-selectivity column
CREATE INDEX idx_products_product_code ON products (product_code);

This index is highly effective because product_code is unique (high selectivity), and the index allows the database to find the exact product row almost instantly.

Less Useful Index Example:
We want to find all products that are currently available for sale.

-- The Query
SELECT * FROM products WHERE is_available = TRUE;

-- The Problematic Index
CREATE INDEX idx_products_is_available ON products (is_available);

This index is not very useful. The is_available column has low selectivity (only two values: true and false). If 90% of products are available, the database will likely ignore this index and perform a full table scan, as it's more efficient.

Checklist: When to Add an Index

Before adding an index, ask yourself these questions:

1. Is the table large? (Indexes on small tables have little to no benefit).
2. Is a specific query running slowly? (Don't add indexes blindly; add them to fix a known performance problem).
3. Am I filtering on this column frequently in a WHERE clause or using it in a JOIN? (These are the primary candidates for indexing).
4. Does the column have high selectivity? (Will the index significantly narrow down the number of rows to check?).
5. For a composite index, does my query use the leftmost column(s) of the index?
6. Is this a read-heavy table? (If the table has very frequent writes but few reads, the cost of maintaining the index might outweigh the read benefits).