Indexes and Query Performance

An index is a sorted data structure that lets MySQL find rows without scanning the entire table. The right indexes can turn a slow query into an instant one.

Use EXPLAIN to see how MySQL plans to execute a query before adding indexes:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

The type column in EXPLAIN output is the key indicator. From best to worst: const > ref > range > index > ALL. A full table scan (ALL) on a large table is the problem you want to fix.

Add a simple index on a column used in WHERE or JOIN conditions:

CREATE INDEX idx_orders_customer ON orders(customer_id);
-- or at table-creation time:
ALTER TABLE orders ADD INDEX idx_status (status);

A composite index covers multiple columns. Column order matters: put the most selective (highest cardinality) column first, and match the order you use in WHERE clauses:

CREATE INDEX idx_orders_customer_date
    ON orders(customer_id, created_at);

-- This query can use the composite index:
SELECT * FROM orders WHERE customer_id = 5 AND created_at > '2025-01-01';

Don't over-index. Every index speeds up reads but slows down writes (INSERT/UPDATE/DELETE) because MySQL must update the index too. Use EXPLAIN to confirm an index is actually being used.