Skip to main content
PProfileMatch
Read Play

Database Indexing Tutorial

Understand B-trees, composite indexes and query plans, and know when an index helps or hurts

Intermediate4 lessonsabout 24 min of reading

This course is for developers who already write SELECT, JOIN and WHERE and now want to know why one query returns in a millisecond and its twin takes a minute. It assumes the basics from SQL Fundamentals. The examples run on SQLite, which ships with Python, so you can reproduce every plan and measurement yourself.

You will build a toy index and count its work, learn how composite and covering indexes depend on column order, read query plans, and measure what indexes cost on writes and storage. PostgreSQL and MySQL equivalents are explained in words and syntax; they were not run for this course, so their outputs are described, not shown. Pair it with Advanced SQL for the query shapes that make good use of the indexes.

The four lessons go from mechanism to design to diagnosis to cost: how a B-tree makes lookups cheap, how to order columns in a composite index, how to read a plan and confirm the index is used, and when an index is not worth having.

Start lesson 1: Database IndexingRide it: tricks, quizzes, XP

Database Indexing lessons

  1. 1Database Indexing: How a B-Tree Index Speeds Up LookupsHow database indexing works: build a toy B-tree-style index in Python, compare its page reads with a full scan, and see why the tree stays shallow.7 min read
  2. 2Composite and Covering Indexes: Column Order in Database IndexingHow composite index column order works, the leftmost-prefix rule and covering indexes, shown with real SQLite EXPLAIN QUERY PLAN output you can run yourself.5 min read
  3. 3Reading Query Plans in Database Indexing: EXPLAIN in PracticeLearn to read EXPLAIN query plans for database indexing: SCAN vs SEARCH, join order, ORDER BY sorts, plus how PostgreSQL and MySQL output differ from SQLite.6 min read
  4. 4Database Indexing Costs: Write Overhead, Bloat and When Not to IndexThe cost of database indexes: slower writes, extra storage and bloat measured in SQLite, plus partial indexes, when not to index and a maintenance checklist.6 min read

Preparing for an interview? Practise SQL & Databases interview questions

Learning Database Indexing to land a job? Browse open roles or check which skills are trending.