DBMS

Free Indexes, queries and scale lessons

9 lessons, about 2 hours in all. Each teaches one idea with a visual, then gives you a problem or a few questions on it. Read the lessons here, then create a free account to practise and keep your progress.

Start free with lesson 1 See the full Indexes, queries and scale path

Stage 0Where the data lives

Rows, pages and the cost of a scanSee how rows are packed into fixed-size pages, why the page is the unit of cost, and how to estimate a full scan.12 min
The buffer poolHow a cache of pages in memory turns most disk reads into memory reads, and why hit ratio dominates average cost.11 min

Stage 1Indexes

B+ tree indexesSee how a wide, shallow tree finds one row in a few page reads, and why its height barely changes as the table grows.14 min
Composite, covering and selective indexesPick an index for a real query: the order of columns, whether it can cover the query, and whether it will be used at all.14 min

Stage 2Executing queries

Query plans and join algorithmsRead a plan as a tree of operators, and compare nested-loop, block nested-loop and hash joins by their page cost.15 min
N+1 queries and finding what's slowThe access patterns that make an app slow even with good indexes, and a method for diagnosing a slow query.12 min

Stage 3Scaling out

Replication and shardingTwo ways to go beyond one machine: copies for reads and availability, and splitting data by key for writes and size.15 min
Caching and the CAP trade-offCache patterns and their staleness, and what CAP actually forces you to choose when the network splits.14 min
Choosing a data storeThe families of non-relational stores, what each is good at, and a way to decide between them and a relational database.12 min

All of Indexes, queries and scale is free

That’s the whole topic, from the first idea to the mastery stage, with no card needed. Create a free account to practise, keep your progress and carry on to the next topic.

Create your free account