Plans & performance
17 steps in 5 sets of SQL.
Reading EXPLAIN, which is the difference between guessing and knowing. Then indexes, including composite, partial and covering ones, then rewriting queries so the planner can use them.
Transactions close it out. Seventeen steps, and the recurring lesson is that a query is fast because the planner found an index, not because it looked tidy.
Reading a plan
- Reading a query planThe plan is the engine telling you what it is about to do. Read it before you guess.
- Giving the planner statisticsThe planner guesses row counts. ANALYZE replaces the guess with a measurement.
Indexes
- IndexesAn index trades write speed and disk for fast lookups on a column.
- Composite indexes and column orderA two-column index is one ordered list, so the leading column is the one you can search.
- Partial indexesIf the query always filters the same way, the index does not need the other rows.
- Indexing an expressionYou cannot index a column and then search a function of it. Index the function instead.
- Covering indexesWhen the index already holds every column the query wants, the table is never touched.
Writing it faster
- Predicates an index can useWrap a column in a function and the index stops being an index.
- EXISTS, IN and the NULL trapThe two read alike until the subquery returns a NULL, and then they disagree.
- One query, three plansThe same question asked three ways, with the plan after each — the difference is the lesson.
Transactions
- TransactionsA transaction makes several statements land together or not at all.
- SavepointsA savepoint is a transaction inside a transaction, so one step can fail alone.
- UpsertINSERT ON CONFLICT writes a row or updates the one already there.
- How a write can handle a conflictIgnore it, replace the row, or update the parts you meant to update.
- Connection pragmasA handful of pragmas decide how a SQLite connection trades safety for speed.
Encore
- index_review.sqlAsking the database what indexes it has, which ones it uses, and which ones it is only paying for.
- slow_query_clinic.sqlOne report written three ways: a correlated subquery, a join, and a window function.