An index turns a query from “scan every row” into “jump straight to the matching rows” — the single biggest lever for MySQL query performance, and one of the most misunderstood.
-- Without an index, this scans the entire table SELECT * FROM orders WHERE customer_email = 'alice@example.com'; -- Add an index on the column you filter by CREATE INDEX idx_customer_email ON orders(customer_email); -- Composite index for queries filtering on multiple columns together CREATE INDEX idx_status_date ON orders(status, created_at); -- Check whether a query is actually using an index EXPLAIN SELECT * FROM orders WHERE customer_email = 'alice@example.com';
Indexes Aren’t Free
Every index speeds up reads but slows down writes (each INSERT/UPDATE must also update every index), and consumes disk space. Index the columns you actually filter, join, or sort by — not every column “just in case.”