Why not index every column?

Each index consumes storage and increases write cost because inserts, updates, and deletes must also update the index structure.

What makes a composite index useful?

A composite index helps when queries commonly filter or sort on the same set of columns in a consistent order.

Database Indexing - System Design

Why It Matters

Without indexing, databases scan entire tables.

At scale, this kills performance.

Example:

Table size: 10 million rows
Full scan query time: 800 ms to 1200 ms
With index: 10 ms to 30 ms

If your API depends on such queries, your system will slow down fast.

What This Concept Actually Means

Indexing is a way to organize data so that queries can find results faster.

Instead of scanning all rows, database uses an index to jump directly to relevant data.

Think like this:

Without index = reading whole book to find one topic
With index = using table of contents

How It Works

Let’s take a users table.

Total rows: 5 million
Query: find user by email

Without Index

Database scans all 5 million rows
Time: 500 ms to 900 ms

With Index on email

Database directly locates row
Time: 5 ms to 20 ms

Flow:


Diagram100%
flowchart LR
  Query --> Index
  Index --> DataLocation
  DataLocation --> Result
Zoom or open fullscreen for a closer look
visualized by

Key Techniques / Variations

1. Single Column Index

Index on one field.

Example:

index on email

Use case:

2. Composite Index

Index on multiple columns.

Example:

(user_id, created_at)

Use case:

fetching user orders sorted by date

3. Unique Index

Ensures no duplicate values.

Example:

email must be unique

4. Full Text Index

Used for search queries.

Example:

searching product descriptions

5. B-Tree Index (default)

Used in most databases.

balanced tree structure
efficient for range queries

Trade-offs and Design Decisions

When to use indexing

frequently queried fields
large tables (millions of rows)

Pros

reduces query time (800 ms to 20 ms)
improves API response time

Cons

slower writes (insert/update)
extra storage cost

Real-world example

System handles 5,000 queries per second
Each query takes 500 ms without index

After indexing:

Query time: 20 ms
System can handle much higher throughput

Write impact:

Insert latency increases from 5 ms to 15 ms due to index updates

Architecture / Flow Diagram


Diagram100%
flowchart LR
  Client --> API
  API --> Database
  Database --> Index
  Index --> Data
Zoom or open fullscreen for a closer look
visualized by

Failure Modes To Watch

over-indexing causing slow writes
unused indexes wasting memory
wrong column order in composite index
missing indexes on critical queries

Design Checklist

Ask yourself:

Which queries are slow?
Which columns are frequently filtered?
What is read vs write ratio?
Do I need composite indexes?
Are my indexes actually being used?

Summary

Indexing improves query performance drastically
It avoids full table scans
Use it for read-heavy workloads
Too many indexes hurt write performance
Always design indexes based on real queries