Pathwise

Manuals / SQL / Chapter 10

D · Performance · advanced · Week 3–4 · Chapter 10 of 12

Indexes & EXPLAIN

Indexes speed reads, slow writes. B-tree default. EXPLAIN shows query plan. Index columns in WHERE and JOIN.

Path progress
78%

Step 1 of 3

Why indexes

Without index: full table scan. With index on customer_id: fast lookup for JOINs and WHERE customer_id = ?.

Why indexesDrag stickies · tap for tips
Study mapDrag stickies · tap for tipsKeep it shortdrag · tap →Name the waitdrag · tap →Scope locatorsdrag · tap →Trace when stuckdrag · tap →One browser firstdrag · tap →Isolate statedrag · tap →Assert the UIdrag · tap →Retry wiselydrag · tap →Seed datadrag · tap →Close the loopdrag · tap →Keep it shortdrag · tap →Name the waitdrag · tap →Scope locatorsdrag · tap →Trace when stuckdrag · tap →One browser firstdrag · tap →Isolate statedrag · tap →Pathwise hackdrag · tap →Why indexesdrag · tap →Try thisdrag · tap →Follow the dashed drag · tap →

Do this now

Read Use The Index, Luke — chapter 1. Note one insight.

Was this step clear?
Chapter learning outcomes
  • CREATE INDEX
  • EXPLAIN / EXPLAIN ANALYZE
  • When to index
  • Covering indexes lite

Clear these before you leave

Side quest

Composite index

Index (status, created_at) for WHERE status=? ORDER BY created_at.