Without an index the database reads every row to answer a question. With the right one it jumps straight to the answer. The difference on a large table is between seconds and microseconds - and the wrong index is worse than none, because it costs writes and gives nothing.

Index what you FILTER on

The WHERE clause, then ORDER BY, then the join columns. Not what you SELECT - that is not what the search uses.

SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;\nCREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

Order matters in a composite index

A composite index is usable left to right, like a phone book sorted by surname then first name. Sorted that way you can find everyone called Ali; you cannot find everyone whose first name is Mohamed.

  • (customer_id, created_at) serves a filter on customer_id, and on both.
  • It does NOT serve a filter on created_at alone.
  • Put the column you filter by exactly first, and the range or sort second.

Prove it is being used

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

type: ref or const means the index is used. type: ALL means a full scan, and the index is being ignored - usually because the column is wrapped in a function.

WHERE DATE(created_at) = "2026-01-01" cannot use an index on created_at: the function has to run on every row first. Write it as a range instead: created_at >= "2026-01-01" AND created_at < "2026-01-02".

Why not index everything

  • Every index is updated on every INSERT, UPDATE and DELETE. Ten indexes make writes several times slower.
  • They take disk, sometimes more than the table.
  • The planner has more choices and occasionally picks a worse one.
Add the index the slow log named, measure, and stop. Adding indexes speculatively is how a database gets slower while looking like it was tuned.