Database indexes, explained with a phone book
Every slow query I have ever fixed came down to the same picture. You ask MySQL for WHERE email = 'omar@example.com' and, without an index, it does the only thing it can:
What an index actually is
An index is a second, sorted structure next to your table: a B-tree. Sorted things can be searched by halving, so the database takes hops, not steps. Same query, with an index on email:
When to add one
- Columns in
WHERE,JOIN ... ON, andORDER BYof your hot queries. - Foreign keys, always.
- Not on columns you almost never filter by: every index slows down writes a little.
Before guessing, run
EXPLAIN on the query. If you see type: ALL, that is the first sketch: a full scan. After the right index it becomes ref or range, the second sketch.The 20% performance win I shipped at Getmayes was mostly this: reading EXPLAIN output, drawing the tree, and adding the four indexes the queries were begging for.
Notes from readers