What actually happens in a database index (and why half of them do nothing)

The same query takes 4 seconds one day and 4 milliseconds the next — you added one line, an index. Here's what an index really is (a B-tree), why it turns a scan into a three-hop walk, and the one rule that decides whether yours even gets used.