The single most common performance fix.
WHAT AN INDEX DOES
Lets the database find matching rows without examining every row. Without one, a query on a table with a million rows reads a million rows.
WHERE THEY ARE NEEDED
Columns used in WHERE clauses Columns used in JOIN conditions Columns used for ORDER BY on large result sets Foreign key columns
FINDING MISSING ONES
Enable the slow query log, or use a profiling tool. The slow queries are almost always the unindexed ones.
EXPLAIN before a query shows how the database will execute it, including whether an index is used. A full table scan on a large table is the thing to fix.
THE COST
Indexes speed reads and slow writes, since each write updates the index. They also consume disk.
Index what you query on, not every column.
THE COMMON SITUATION
A site that was fast at launch and is slow two years later. Nothing changed except the table size, and a query that scanned a hundred rows now scans a hundred thousand.
Adding one index frequently restores it entirely.
