Query optimizers

Understand query plans, estimates, joins, and indexes. Investigate slow SQLite queries.

Prerequisite: Read SQL with WHERE and JOIN. These lessons use SQLite terminology and focus on reading and interpreting plans, not implementing an optimizer.

Lessons

1. 1

   Reading query plans

   Read SCAN, SEARCH, loop order, and temporary sorting. Separate plans from measurements.

2. 2

   How estimates shape a plan

   Understand statistics, uneven data, and related columns. Inspect the statistics behind a plan.

3. 3

   How joins are chosen

   Compare repeated scans, indexed lookups, and automatic indexes. See why loop order matters.

4. 4

   How indexes change the options

   Choose column order, use indexes for sorting, and understand partial and covering indexes.

5. 5

   Investigating slow queries

   Test an explanation, compare fair measurements, and decide whether a change helped.