Indexes
Making queries fast.
Helen's shop has thousands of orders. A big online shop has millions. To find one customer's orders, the database might read every single row. That's slow. An index is like the index at the back of a book. It tells the database exactly where to look.

- Millions of rows
- Reading every row is slow
- An index shows where to look
Without an index
Explain query plan shows how the database will run a query, without running it. Run it. Scan orders means it reads the whole table, row by row.
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;QUERY PLAN `--SCAN orders
With an index
Now create an index on the customer ID column. Then ask for the plan again. Run it. Now it says search, using the index. Instead of reading every row, it jumps straight to customer forty two.
CREATE INDEX idx_orders_customer
ON orders (customer_id);
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42;QUERY PLAN `--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
When to add an index
Indexes make reading faster, but they make inserts and updates a little slower, and they take up space. So add them to columns you often search or join on. Foreign keys, like customer ID, are the classic example.
Summary
An index helps the database find rows fast. Explain query plan shows whether it's being used. Next, how to design good tables from the start.
Key takeaways- CREATE INDEX ... ON t (col)
- SCAN = slow, SEARCH = fast
- Index what you search