System Design Database Indexing

A database index is a shortcut that helps the database find rows quickly. Without an index, the database checks every row one by one. With an index, the database jumps close to the answer in a few steps.

The Book Index Analogy

A thick textbook ends with an index page. A reader who wants the word "cache" looks up the index and sees page 142. The reader skips 141 pages of searching. A database index works the same way. It stores column values in sorted order together with the location of each row.

Full Table Scan vs Index Lookup

Imagine a users table with 10 million rows. A query asks for the user with the email "ravi@example.com".

WITHOUT INDEX
Row 1 -> Row 2 -> Row 3 -> ... -> Row 10,000,000
(checks millions of rows)

WITH INDEX
Index root -> Middle level -> Leaf -> Row location
(checks about 3 to 4 steps)

The scan may take seconds. The index lookup finishes in milliseconds. This gap decides whether an app feels smooth or broken under heavy traffic.

How a B-Tree Index Works

Most databases build indexes as B-trees. A B-tree keeps values sorted and splits them into levels. Each level narrows the search range.

              [ 50 | 100 ]
             /      |      \
     [10|30]     [60|80]    [120|150]
      /  |  \     /  |  \     /   |   \
   leaf  leaf leaf leaf leaf leaf  leaf leaf
   (leaf pages point to the actual rows)

A search for 80 starts at the root. The root shows that 80 sits between 50 and 100, so the search moves to the middle branch. The lower level pinpoints the leaf that holds 80.

Types of Indexes

Primary Index

The database creates this index automatically on the primary key. Every row has a unique key value, and lookups by ID run fast.

Secondary Index

Engineers add secondary indexes on other columns, such as email or city. One table can hold many of them.

Composite Index

A composite index covers two or more columns together. An index on (city, age) speeds up queries that filter by city and age. Column order matters. The index helps queries that start with city and has little value for queries that filter by age alone.

Unique Index

A unique index blocks duplicate values. It protects columns such as email or phone number.

Full-Text Index

A full-text index supports word searches inside long text, such as blog posts or product descriptions.

The Cost of Indexes

Indexes are not free. Every insert, update, or delete must change each related index too. A table with ten indexes writes slower than a table with two. Indexes consume disk space too. Smart teams balance read speed against write speed.

Index Comparison

Situation	Index Helps?
Searching by email in a large table	Yes, big gain
Sorting results by a date column	Yes
Column with only two values, such as yes or no	Rarely
Small table with 100 rows	No visible gain
Table with constant heavy writes	Use few indexes

Best Practices

• Index columns that appear often in WHERE, JOIN, and ORDER BY parts of queries.
• Pick columns with many different values.
• Remove indexes that no query uses.
• Test slow queries with the EXPLAIN command to see whether the database uses an index.
• Keep composite indexes short and ordered by how queries filter.

Indexing in Large Systems

Indexing pairs with other techniques. Sharding splits big tables across servers, and each shard keeps its own indexes. Replicas serve read queries with the same index structure. Caches answer repeated lookups before the database sees them. Together these tools keep large systems fast as data grows.