How indexes work

Without an index the engine reads every row — a full scan. An index is a sorted structure on one or more columns that lets the engine jump straight to the matching rows, the same way a book index beats reading every page.

Index the columns you filter and join on, not the ones you only display. A composite index on (a, b) also serves lookups on a alone, but not on b alone — column order matters.

Indexes are not free: every INSERT, UPDATE and DELETE has to maintain them, and they take disk space. Index selectively rather than everywhere.

Low-selectivity columns (a status with three values) rarely benefit from their own index — the engine often decides a scan is cheaper.