Understanding Database Indexes: A Visual, Hands-On Guide

Database Indexes

Introduction

If you have ever stared at a slow query and wondered why it takes seconds when the table has “only” a few million rows, the answer is almost always the same: a missing index — or a misunderstood one.

This article walks through database indexes visually and practically. We will cover full table scans, how a B-Tree index navigates to a value, range searches, and the composite index left-prefix rule — using a concrete employees table throughout so every example is grounded in real data.

The employees table we will use

Every example in this article uses the same eight-row table:

ID   Name    Department    Salary
-----------------------------------
1    Alice   HR            $45,000
2    Bob     Engineering   $55,000
3    Carol   Engineering   $72,000
4    David   Marketing     $78,000
5    Eve     HR            $60,000
6    Frank   Engineering   $89,000
7    Grace   Marketing     $65,000
8    Zoe     Engineering   $98,000

Small enough to reason about clearly, but large enough that you can see exactly what the database is doing at each step.

Part 1 — No index: the full table scan

Consider this query:

SELECT * FROM employees WHERE name = 'Carol';

Without an index, the database has no idea where Carol is. It must read every single row from top to bottom until it finds a match. This is called a full table scan.

Here is what happens step by step:

Row 1: Alice  — not Carol, keep going
Row 2: Bob    — not Carol, keep going
Row 3: Carol  — MATCH FOUND, return row

In this case the database only read three rows before finding Carol. Lucky — she is near the top. But what if we were looking for Zoe? The database would read all eight rows. In a real table with one million employees, finding Zoe could mean one million disk reads.

This is the core problem indexes solve. The time complexity of a full scan is O(n) — as the table doubles in size, the worst-case scan time doubles too.

Part 2 — Index lookup: exact name search

Now add an index on the name column:

CREATE INDEX idx_name ON employees(name);

The database builds and maintains a separate sorted structure — the index — that maps each name to the physical location of its row. The index entries look like this, sorted alphabetically:

Name (key)    Row pointer
-------------------------
Alice         row_1
Bob           row_2
Carol         row_3
David         row_4
Eve           row_5
Frank         row_6
Grace         row_7
Zoe           row_8

Because the index is sorted, the database can use binary search to find Carol in just three steps instead of scanning all eight rows:

Step 1: Check middle entry "David" — Carol comes before David → go left
Step 2: Check "Bob" — Carol comes after Bob → go right
Step 3: Check "Carol" — MATCH FOUND → follow row pointer to row_3

Three steps versus up to eight in a full scan. The time complexity is now O(log n). With one million rows, that is roughly 20 comparisons instead of one million. That is the power of an index.

Part 3 — Range search: salary between X and Y

Exact lookups are straightforward. Range queries are where indexes really shine — and where many engineers are surprised by how efficient they are.

Consider this query:

SELECT * FROM employees WHERE salary BETWEEN 60000 AND 85000;

First, add an index on salary:

CREATE INDEX idx_salary ON employees(salary);

The index is now sorted by salary:

Salary(key)  Name     In range?
---------------------------------
$45,000      Alice    below range
$55,000      Bob      below range
$60,000      Eve      in range ✓
$65,000      Grace    in range ✓
$72,000      Carol    in range ✓
$78,000      David    in range ✓
$89,000      Frank    above range, stop
$98,000      Zoe      above range, stop

Here is the key insight: the database does not check every row. It uses binary search to jump directly to the first salary that is at least $60,000, then scans forward in the sorted index until it hits a value above $85,000 — and stops immediately. It never even looks at Frank or Zoe beyond recognizing they are out of range.

This is called an index range scan. Without the index, the database would check all eight rows regardless of the range. With the index, it reads only the rows that are relevant. The wider the range, the more rows it reads — but it never reads rows it does not need.

Part 4 — The B-Tree: how the index navigates

Under the hood, most database indexes are stored as a B-Tree (Balanced Tree). Understanding the tree structure explains why index lookups are fast and how range scans work so naturally.

A B-Tree index on salary might look like this:

                [72,000]
               /        \
         [50,000]      [89,000]
         /     \        /     \
    [45,000] [55,000] [78,000] [98,000]

To find the salary $45,000, the database walks the tree:

Step 1: Root is 72,000 — target 45,000 is less → go LEFT
Step 2: Node is 50,000 — target 45,000 is less → go LEFT
Step 3: Node is 45,000 — MATCH FOUND

Three steps to find a value in a tree of seven nodes. The tree is always balanced, which guarantees that no path from root to leaf is longer than any other. This balance is what keeps lookups at O(log n) regardless of which value you are searching for.

Range scans are a natural fit for B-Trees because once the database finds the start of the range in the tree, the leaf nodes are linked together in sorted order. It can scan forward through the leaves without going back up the tree — which is why range queries on indexed columns are so efficient.

Part 5 — Composite indexes and the left-prefix rule

A composite index covers multiple columns:

CREATE INDEX idx_dept_salary ON employees(department, salary);

The index sorts entries first by department, then by salary within each department:

Department     Salary    Name
------------------------------
Engineering    $55,000   Bob
Engineering    $72,000   Carol
Engineering    $89,000   Frank
Engineering    $98,000   Zoe
HR             $45,000   Alice
HR             $60,000   Eve
Marketing      $65,000   Grace
Marketing      $78,000   David

Now here is the rule that trips up many engineers: the index can only be used if the query includes the leftmost column in the index definition. This is called the left-prefix rule.

Query 1 — department AND salary (index fully used):

SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 70000;

Both columns match the left prefix. The database jumps directly to Engineering rows in the index, then filters by salary within that group. Very efficient.

Query 2 — department only (index partially used):

SELECT * FROM employees WHERE department = 'HR';

Only the first column is used, but that is fine. The index is sorted by department first, so the database can still jump directly to HR rows. The index is used, just not for the salary part.

Query 3 — salary only (index NOT used):

SELECT * FROM employees WHERE salary > 70000;

This query skips the first column entirely. Because the index is sorted by department first, salaries are not globally sorted across the whole index — they are only sorted within each department group. The database cannot use this index to find high salaries efficiently. It falls back to a full table scan.

The fix is simple: if you frequently query by salary alone, create a separate single-column index on salary. Composite indexes are not a replacement for single-column indexes — they serve different query patterns.

When indexes help and when they hurt

Adding an index is not always the right move. Here is a practical guide:

Indexes help when:

The column has high cardinality — many unique values like email addresses, user IDs, or phone numbers. The column appears frequently in WHERE clauses, JOIN conditions, or ORDER BY clauses. You are running range queries on numeric or date columns.

Indexes hurt when:

The table has heavy write traffic. Every INSERT, UPDATE, and DELETE must also update every index on the table. Ten indexes on a high-write table can multiply your write time tenfold. The column has low cardinality — a boolean column with only two possible values gives the index almost nothing to work with, and the database may ignore it anyway. The table is very small — for a few hundred rows, a full scan is often faster than the overhead of an index lookup.

Index gotchas that surprise engineers:

Wrapping a column in a function kills the index:

-- Index on email is ignored — function wraps the column
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- Index is used — no function on the column
SELECT * FROM users WHERE email = 'john@example.com';

A leading wildcard in a LIKE query forces a full scan:

-- Full scan — leading wildcard
SELECT * FROM users WHERE name LIKE '%john%';

-- Index used — trailing wildcard only
SELECT * FROM users WHERE name LIKE 'john%';

Type mismatches can cause the index to be skipped silently:

-- phone_number is VARCHAR but we pass an INT — index may be skipped
SELECT * FROM users WHERE phone_number = 1234567890;

-- Match the type — index is used
SELECT * FROM users WHERE phone_number = '1234567890';

How to check if your index is being used

Use EXPLAIN before any query you are trying to optimize:

EXPLAIN SELECT * FROM employees WHERE name = 'Carol';

The key things to look for in the output:

• Index Scan — the index is being used, good
• Seq Scan (or full table scan) — no index used, investigate why
• Index Only Scan — the best case, the data was fetched entirely from the index without touching the table
• Bitmap Heap Scan — index used for a larger result set, normal for range queries

If you see a sequential scan on a column you expected to be indexed, check for the gotchas above: function wrapping, type mismatch, leading wildcard, or the left-prefix rule on a composite index.

Summary

Situation                                 | Use index?
----------------------------------------------------------
High cardinality column (email, ID)       | Yes
Frequently filtered column                | Yes
JOIN column                               | Yes
Range queries on numeric or date columns  | Yes
Low cardinality column (boolean, status)  | No
Very small table                          | No
Heavy write table (logs, events)          | Be careful
Column wrapped in a function              | Index ignored
LIKE with leading wildcard                | Index ignored
Composite index without leftmost column   | Index ignored

Final thoughts

Indexes are the single most impactful performance tool available in a relational database — but they come with real trade-offs. The engineers who get the most out of them are not the ones who add indexes to every column. They are the ones who understand the query patterns, measure with EXPLAIN, and make deliberate decisions about which columns genuinely need them.

Next time a query is slow, before rewriting the logic, check if the right index is in place. It might be a one-line fix.

If you found this useful, follow me for more articles on backend engineering, databases, and software design.