Indexes, Query Plans and Maintenance - Database Cheat Sheet

SCAN against SEARCH on real rows, then EXPLAIN QUERY PLAN before and after CREATE INDEX, composite and unique indexes, the substr() trap, LIKE and indexes, a VACUUM INTO backup proven by row counts, RPO and RTO, and upkeep.

On the sheet:

- SCAN vs SEARCH: every row read, against an index that jumps to the matches

- EXPLAIN QUERY PLAN: the same query before and after CREATE INDEX

- Keys and UNIQUE indexes: free indexes, and a duplicate refused

- Composite index: the left-most rule

- Keep the column bare: why substr() forces a scan

- LIKE and indexes: a fixed start, and the index it needs

- Lean queries: name the columns, filter early, and the join order

- Backups, RPO and RTO: full, incremental and differential

- VACUUM INTO and upkeep: a backup proven by row counts, then VACUUM and ANALYZE

Two A4 pages, printed front and back, sized to print once and keep beside you while you work. Written in short sentences and plain English, for readers whose first language is not English and clearer for everyone else because of it.

It pairs with the Speeding It Up: Indexes & EXPLAIN, Query Optimization, Indexing & Query Optimization and Backup, Recovery & Maintenance books, so you can learn each idea in full and then revise it here in two pages.

Every query on this sheet was run against a live database before the page was printed, so the result you read is the result you get. Instant PDF download. By M.R. Sterling.