The one-word SQLite fix
A well-known explainer of how computers work spent four years running SQLite in production before discovering a built-in command that took one slow search from five seconds to a twentieth of a second.
Julia Evans has built a career explaining how software works from the inside. So there is something bracing about her admitting, in a post about running Django sites on SQLite, that she only just learned what the ANALYZE command does. She typed it once. A full-text search over a 4,000-row table dropped from about five seconds to about 0.05 seconds, small enough, she writes, that she stopped measuring. ANALYZE simply tells SQLite's query planner what the data looks like, so it stops guessing which path through a query is fastest. It ships in the box. She had used SQLite for a web project since 2022 without ever running it.
"Immediately the problem query went from taking 5 seconds to like 0.05 seconds (or some other number small enough that I didn't care to investigate further)." — Julia Evans
The rest of the post is a clear-eyed field log of the same kind: a delete that runs longer than five seconds silently crashes any other write waiting on SQLite's single-writer lock, so she now deletes in small batches; backups go out via a plain copy to S3; where two tables never needed a shared transaction she split them into separate database files. None of it is new to anyone who has run SQLite in anger. What makes it land is the frame she puts on it, that even a systems writer takes years to find the basics.
The coda is the fun part. Reading the line where Evans confessed she still can't read a query plan, Simon Willison replied Big same, then had an AI help him build a fix: a free browser page that runs real SQLite entirely on your machine, takes any query, and annotates the database's own execution plan in plain English. He is candid that he can't fully vouch for the explanations. But the pairing is the whole story of learning a tool, a public admission of a gap and, a day later, something built to close it.
Open Willison's SQLite Query Explainer in the browser, paste a slow query, and read the plain-English notes on why SQLite runs it the way it does.
Get the tool at jvns.ca →The lenses
The facts
Concepts
How this connects
Tap a node to open it