🗂️Database Indexing

Indexes trade write cost and storage for fast reads.

📌 Key IdeaAdd indexes that match query predicates and sort order. Avoid over-indexing: each extra index slows INSERT/UPDATE/DELETE and consumes memory/disk.

🧩

Problem it solves

Without indexes, databases scan too many rows for lookups and ordered queries.
As data grows, full scans and sorting increase latency and cost dramatically.

✨

Why It Matters

Index design is one of the biggest levers for API response time and DB capacity.
Good indexing avoids expensive query rewrites and premature infrastructure scaling.

🖼

Visual

🗂️ Index narrows search path

Without index: full table scan O(n)
With B-Tree: seek + range scan O(log n + k)

Good index matches WHERE + ORDER BY pattern.

✅

When to Use

Columns frequently used in WHERE, JOIN, ORDER BY.
High read-to-write ratio tables.
Latency-critical lookups by business keys (email, order_id).

🚫

When NOT to Use

Tiny tables where full scan is already cheap.
Low-cardinality columns as standalone index.
Write-heavy hot tables with many rarely-used indexes.

🚨

Anti-Pattern (What NOT to Do)

Query pattern not supported by indexBadjava

String sql = """
SELECT id, status, created_at
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC
LIMIT 20
""";

If only index(customer_id) exists, DB may still sort many rows.

✅

The Right Way

Composite index aligned with queryGoodjava

-- SQL migration
CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);

-- Java query remains same and now uses index order

Composite index avoids extra sort and speeds top-N queries.

🎤

Interview Q&A

🧾

Quick Cheat Sheet

Design ruleStart from query patterns, not schema aesthetics.

Composite orderEquality columns first, range/sort next.

ValidateAlways check execution plans.

CostFaster reads, slower writes.

🔗