Database Indexing

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:

Diagram
flowchart LR
  Query --> Index
  Index --> DataLocation
  DataLocation --> Result
visualized by

Key Techniques / Variations

1. Single Column Index

Index on one field.

Example:

• index on email

Use case:

• login systems

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

Diagram
flowchart LR
  Client --> API
  API --> Database
  Database --> Index
  Index --> Data
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