Health Data Clinic - Book 16: Indexing & Query Optimization

A correct query that takes ten seconds is still a problem. This book is about speed, and it begins with what an index actually is and the difference between a full table scan and a search. You create indexes, then read EXPLAIN QUERY PLAN to see what the database chose to do, which turns tuning from guesswork into evidence. Single-column, composite and unique indexes each get their own treatment, along with the cost an index carries and when dropping one is the right call. The second half is query optimization: writing an efficient SELECT, the way a wildcard in a WHERE clause changes everything, why wrapping a column in a function quietly defeats the index you built, and what makes a JOIN slow, so a query across three tables does not fall off a cliff. PRAGMA and ANALYZE close the book, so SQLite holds the statistics it needs to choose well.

Twenty-five A4 pages, in full color. Written in short sentences and plain English, for readers whose first language is not English and clearer for everyone else because of it.

Every query in this book 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.