Indexes and Query Performance

An index is a lookup structure that lets the database find rows without scanning the whole table. It speeds up reads and slightly slows writes.

Why Indexes Help

Without an index, a filter on email reads every row. With an index, the database jumps close to the matching row and reads only what it needs.

Creating an Index

Index columns you filter, join or sort by often. A composite index can serve several queries if the most selective column comes first.

CREATE INDEX idx_students_email ON students (email);
CREATE INDEX idx_sales_product_date ON sales (product, sold_at);

Reading a Query Plan

Put EXPLAIN before a query to see how the database will run it. Look for full table scans on large tables and missing index notices.

EXPLAIN
SELECT name
FROM students
WHERE email = 'amina@example.com';

Costs

Every index uses disk space and adds work to INSERT and UPDATE. Remove indexes nothing uses. Never index every column by default.

Practice

1. Add an index to the score column of students.
2. Run EXPLAIN on a query that filters by level.
3. List two queries in a school system that would benefit from an index.

Hands-on exercise

A users table is slow to search by email.
1. Write the CREATE INDEX statement.
2. Write an EXPLAIN query that filters by email.
3. In one sentence, explain what changed.

Recap

Indexes make reads fast by avoiding full scans. Index common filters and joins, check with EXPLAIN and remove unused indexes.