Indexes and Query Performance
Indexes and Query Performance
Why Small Tables Are Fast and Big Ones Are Not
A table with a million rows scans slowly if every query has to inspect every row. An index is a sorted structure that lets MySQL jump straight to the rows that match, the same way the index at the back of a book finds a topic without reading every page.
How an Index Speeds a Query
Without an index, a WHERE clause requires a full table scan. With a B-tree index on the column, MySQL can locate the start of the matching range and walk through it, which is dramatically faster.
CREATE INDEX idx_orders_customer ON orders (customer_id);
The PRIMARY KEY is indexed automatically, and most UNIQUE constraints create an index as well. Columns used often in WHERE, JOIN, and ORDER BY are prime candidates for extra indexes.
The Cost of Indexes
Indexes are not free. Every INSERT, UPDATE, and DELETE must maintain every index on the table, which slows writes. They also consume disk space and memory. The right approach is balanced: index the columns your queries actually filter on, and avoid piling indexes on rarely used columns.
Composite Indexes and Prefixes
An index can cover several columns, and column order matters. An index on (state, city) helps queries filtering by state alone, and by both, but not efficiently by city alone. For long text columns, a prefix index indexes only the first N characters to save space.
CREATE INDEX idx_full_name ON users (last_name, first_name);
Analyzing Slow Queries
EXPLAIN shows MySQL's plan for a query, including whether it uses an index or scans the whole table. Refactor when you see a full scan on a large table that you query often. Check EXPLAIN after every index change to confirm the optimization actually took effect.
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
Key Points
- Indexes let MySQL find rows without scanning the whole table.
- Primary keys and unique constraints are indexed automatically.
- Each index slows writes and costs storage, so index deliberately.
- Composite indexes help only when queried in column order.
- EXPLAIN reveals whether a query actually uses your indexes.
