An index is like the index at the back of a book.
Direct answer
A database index lets the database find rows without reading the whole table. Add indexes on columns you frequently filter, join or sort by, especially on large tables. They speed up reads but add cost to writes and storage, so add them deliberately and check with EXPLAIN.
Example
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
When indexes help
- Filtering by a column on a big table.
- Joining on a foreign key.
- Sorting by a column.
When they do not
- Tiny tables.
- Columns with very few distinct values.
- Tables with very heavy writes.
Tips
- Primary keys are indexed automatically.
- Index foreign keys used in joins.
- Measure before and after.
Where Teyro fits
Teyro's SQL and Databases for Beginners course turns this into short lessons you can play in a few minutes a day. It has 50 lessons, and you can start free.
The bottom line
Index what you query most, and prove it helps.
