Level 6 of 13 · about 2 weeks
Query processing and optimization
The stages between a SQL statement and a result set: turning SQL into a query plan, the different join algorithms an optimizer can pick, sorting costs, cost-based optimization and why its estimates can be wrong, writing index-friendly queries, pagination that doesn't slow down on later pages, and materialized views.
Modules
Core: everyone needs it. Common: most jobs use it. Specialist: optional depth, safe to skip on a first read.
1
From SQL to a query plan
the stages a query statement passes through before any row comes back
Common 5 parts · about 2 hours
2
Join algorithms
three different ways to combine two tables, and when the optimizer reaches for each
Core 5 parts · about 2 hours
3
Sorting and the cost of ORDER BY
when a sort is nearly free, and when it becomes one of the most expensive parts of a query
Common 5 parts · about 2 hours
4
Cost-based optimization
how the optimizer estimates which physical plan is cheapest, and why those estimates can be wrong
Common 5 parts · about 2 hours
5
Writing queries the optimizer can use
small changes in how a condition is written that decide whether an index can even be considered
Core 5 parts · about 2 hours
6
Pagination done right
why OFFSET gets slower on every later page, and what to use instead
Core 5 parts · about 2 hours
7
Materialized views
precomputing an expensive query's result once, and deciding how often to recompute it
Common 5 parts · about 2 hours