Back to library

🗂️Database Indexing Explained

Stop adding indexes by trial and error and start designing them from first principles — see why B-trees beat full scans for range queries, read EXPLAIN plans like a pro, and index a real schema without slowing down every write.

Applied14 drops~2-week path · 5–8 min/daytechnology

Phase 1How B-Trees Actually Work

See why B-trees rule reads and what they cost on writes

4 drops

  1. An index is a phone book your database can binary-search

    6 min

    An index is a phone book your database can binary-search

  2. B-trees are tree-shaped binary search, not regular trees

    7 min

    B-trees are tree-shaped binary search, not regular trees

  3. Sorted leaves are why ranges and ORDER BY come free

    6 min

    Sorted leaves are why ranges and ORDER BY come free

  4. Indexes only help when they cut the haystack down hard

    7 min

    Indexes only help when they cut the haystack down hard

Phase 2Adding Indexes and Measuring with EXPLAIN

Add indexes to slow queries and prove it with EXPLAIN

5 drops

  1. EXPLAIN tells you what the database is going to do

    7 min

    EXPLAIN tells you what the database is going to do

  2. The first index to add is on the WHERE clause column

    6 min

    The first index to add is on the WHERE clause column

  3. An index that holds every column the query needs is a free win

    7 min

    An index that holds every column the query needs is a free win

  4. Index only the rows that matter and skip the rest

    7 min

    Index only the rows that matter and skip the rest

  5. Index the function output, not the column

    7 min

    Index the function output, not the column

Phase 3Composite Indexes Match WHERE Clauses

Order composite-index columns to match real WHERE clauses

4 drops

  1. Column order matters more than you think

    7 min

    Column order matters more than you think

  2. An index on (a, b, c) can serve any leftmost prefix

    7 min

    An index on (a, b, c) can serve any leftmost prefix

  3. Once you hit a range, the rest of the index can't filter

    7 min

    Once you hit a range, the rest of the index can't filter

  4. Indexes age — measure them, drop the dead ones

    7 min

    Indexes age — measure them, drop the dead ones

Phase 4Index a Real Schema Without Over-Indexing

Index a real schema end-to-end without over-indexing

1 drop

  1. Index a real schema and defend every index you add

    8 min

    Index a real schema and defend every index you add

Frequently asked questions

What is a database index in plain English?
This is covered in the “Database Indexing Explained” learning path. Start with daily 5-minute micro-lessons that build from fundamentals to hands-on application.
How does a B-tree index actually speed up queries?
This is covered in the “Database Indexing Explained” learning path. Start with daily 5-minute micro-lessons that build from fundamentals to hands-on application.
When should I use a composite index versus separate indexes?
This is covered in the “Database Indexing Explained” learning path. Start with daily 5-minute micro-lessons that build from fundamentals to hands-on application.
Why does adding more indexes slow down writes?
This is covered in the “Database Indexing Explained” learning path. Start with daily 5-minute micro-lessons that build from fundamentals to hands-on application.
How do I read an EXPLAIN plan to know if my index is being used?
This is covered in the “Database Indexing Explained” learning path. Start with daily 5-minute micro-lessons that build from fundamentals to hands-on application.

Related paths

🐍Python Decorators Introduction

Build one mental model for Python decorators that covers closures, argument passing, functools.wraps, and stacking — then ship a working caching or logging decorator from scratch in under 30 lines.

Applied~2-week path · 5-8 min/day

🦀Rust Lifetimes Explained

Stop reading `'a` as line noise and start reading it as scope arithmetic — one failing snippet at a time — until you can thread lifetimes through a small parser or iterator adapter without fighting the borrow checker.

Applied~2-week path · 5-8 min/day

☸️Kubernetes Core Concepts

Stop drowning in 30+ resource types. Build the mental model one primitive at a time -- pods, deployments, services, ingress, config -- then deploy a real app with rolling updates and health checks.

Applied~2-week path · 5-8 min/day

📈Big O Intuition

Stop treating Big O as math you memorized for an interview — build the intuition to spot O(n²) disasters, pick the right data structure without thinking, and rewrite a slow function from O(n²) to O(n) in under five minutes.

Foundations~2-week path · 5-8 min/day