So you want the right ones, not every column indexed.
What indexes do
An index is a sorted lookup structure (B-tree in InnoDB) that helps MySQL find rows without scanning the whole table. Primary keys and UNIQUE constraints create indexes automatically. Foreign key columns often need indexes too – MySQL may require them for referential integrity performance.
Without an index on orders.customer_id, finding all orders for a customer means reading every row in orders. With an index, MySQL jumps straight to matching rows.
Single-column indexes
Index columns you filter or join on frequently:
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_customers_email ON customers (email);Do not index every column. Low-cardinality columns alone (like a boolean is_active on a huge table) rarely help unless combined with other filters.
Composite indexes
When queries filter on multiple columns together, one composite index often beats two separate indexes:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
-- Uses the index well
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending';
-- Uses the index for customer_id only (leftmost prefix rule)
SELECT * FROM orders WHERE customer_id = 42;Column order matters. Put the most selective or most commonly filtered column first – usually the one that narrows results fastest. If you always filter by status alone but never without customer_id, a different order might suit you better. Match real query patterns.
Covering indexes
If an index contains all columns the query needs, MySQL can answer from the index alone without touching the table – a covering index scan. Faster for read-heavy reports.
CREATE INDEX idx_orders_list ON orders (customer_id, placed_at, status);
SELECT id, placed_at, status
FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 20;Check with EXPLAIN (covered later) – look for Using index in the Extra column.
Indexes and sorting
Indexes can satisfy ORDER BY if the sort columns match the index order. ORDER BY placed_at DESC on an index defined as (customer_id, placed_at) works when you filter by customer_id. Without a matching index, MySQL sorts in memory or on disk – fine for small result sets, painful at scale.
What not to index
- Tiny tables where full scans are cheap anyway
- Columns rarely used in WHERE, JOIN, or ORDER BY
- Wide TEXT/BLOB columns (prefix indexes exist but are niche)
- Tables with heavy writes and almost no reads – every index slows inserts and updates
Reviewing existing indexes
SHOW INDEX FROM orders;
-- Or from information_schema
SELECT index_name, column_name, seq_in_index
FROM information_schema.statistics
WHERE table_schema = 'shop' AND table_name = 'orders'
ORDER BY index_name, seq_in_index;Remove unused indexes when you have evidence – duplicate indexes (same leading column) are a common waste.
Practical workflow
- Index foreign keys and columns you JOIN on
- Add composite indexes for your common filter combinations
- Run EXPLAIN on slow queries before adding random indexes
- Measure after deployment – indexes help some queries and hurt others
The next tutorial covers JOIN patterns you will use every week – the queries your indexes are meant to support.