Indexes and Query Performance

Why queries get slow

Without an index, the database has to scan every single row to find matches for your WHERE clause — this is called a full table scan, and it’s fine for a few hundred rows but becomes painfully slow once a table reaches millions of rows.

Creating an index

CREATE INDEX idx_students_course ON students (course);

-- now this query can use the index instead of a full scan:
SELECT * FROM students WHERE course = 'SQL';

An index works conceptually like the index at the back of a textbook — instead of reading every page to find a topic, you jump straight to the relevant pages. A database index is typically stored as a sorted structure that lets it locate matching rows in a fraction of the time a full scan would take.

Reading an execution plan

EXPLAIN SELECT * FROM students WHERE course = 'SQL';

EXPLAIN shows exactly what the database plans to do to run your query — whether it used your index or fell back to a full table scan, and roughly how many rows it expects to examine. This is the first thing any experienced developer checks when a query feels unexpectedly slow, rather than guessing at the cause.

Indexes aren’t free

Every index speeds up reads on that column, but it also slows down writes — every INSERT, UPDATE, or DELETE now has to update the index in addition to the actual row. Index the columns you filter and join on frequently, not every column in every table; a table with ten unnecessary indexes can actually perform worse overall than one with none.

Composite indexes

CREATE INDEX idx_students_course_score ON students (course, score);

An index on multiple columns together speeds up queries that filter or sort by that exact combination (or a matching prefix of it, like course alone) — but it generally won’t help a query that filters on score without also filtering on course, since the column order in the index matters.