What you'll learn

  1. Quick answer
  2. What an index actually is
  3. What to index
  4. Why not index everything
  5. How queries silently defeat an index
  6. Seeing it for yourself
  7. FAQ

Quick Answer

An index is a sorted structure that lets the database find rows without scanning the whole table. Index the columns you filter and join on. Every index slows down writes, so do not add them everywhere, and wrapping an indexed column in a function stops the index being used.

What an index actually is

Without an index, finding every student on course 3 means reading every row in the table and checking. That is a full table scan. With a thousand rows it is instant. With ten million it is not.

An index is a separate sorted structure — usually a B-tree — mapping values to the rows that contain them. Because it is sorted, the database can find the entry by repeated halving rather than by looking at everything, and then jump straight to the matching rows.

CREATE INDEX idx_student_course ON student(course_id);

The textbook analogy is genuinely the right one: the index at the back of a book. Without it you read every page looking for a word. With it you look the word up and turn to the pages listed. The index takes extra space, and it has to be reprinted whenever the book changes — which is exactly the trade-off a database index makes.

What to index

Index the columns that appear in these places:

  • WHERE clauses — the columns you filter on.
  • JOIN conditions — foreign key columns especially. Normalizing your schema creates joins, and unindexed foreign keys are the most common cause of slow ones.
  • ORDER BY — an index can supply rows already in order, avoiding a sort.

Primary keys are indexed automatically. Foreign keys often are not, depending on the database, and that surprises people — it is worth checking rather than assuming.

Columns with few distinct values are poor candidates. An index on a boolean or a gender column rarely helps, because half the table matches and the database may reasonably decide scanning is cheaper.

Why not index everything

Because every index must be kept up to date. An INSERT into a table with six indexes writes the row once and updates six separate structures. The same applies to UPDATE and DELETE.

So indexes trade write speed and disk space for read speed. On a table that is written constantly and read rarely, over-indexing makes the system slower overall. On a table read constantly and written rarely, indexes are close to free.

The practical approach is to add indexes in response to measured slow queries, not in advance. "Add an index to every column" is a real anti-pattern with a real cost, and being able to explain that trade-off is a strong interview answer.

How queries silently defeat an index

The index exists, the query looks right, and the database scans the whole table anyway. Usual causes:

  • A function around the column. WHERE YEAR(created_at) = 2026 cannot use an index on created_at, because the index stores dates, not years. Rewrite as a range: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'.
  • A leading wildcard. LIKE '%son' cannot use an index, because a sorted structure cannot help when you do not know the beginning. LIKE 'John%' can.
  • Type mismatch. Comparing a numeric column to a quoted string may force a conversion on every row.
  • Wrong column order in a composite index. An index on (a, b) helps queries filtering on a, or on a and b — but generally not b alone. Order matters, like sorting a phone book by surname then first name.

Use EXPLAIN before your query to see what the database actually plans to do. It will tell you whether the index is used, and it removes the guesswork entirely. Most developers never run it; the ones who do find these problems in minutes.

Seeing it for yourself

The effect is invisible on small data, which is exactly why it bites in production. To feel it, generate a table with a few hundred thousand rows, time a filtered query, add the index, and time it again. The difference is not subtle.

Then run EXPLAIN on both and compare the plans. Seeing "full table scan" become "index lookup" makes the concept concrete in a way that reading about B-trees does not.

This pairs directly with normalization: splitting tables properly creates joins, and indexing the join columns is what keeps those joins fast.

Frequently Asked Questions

Does a primary key create an index automatically? Yes, in every mainstream relational database. Foreign keys often do not, which is a frequent cause of slow joins, so check your database's behaviour rather than assuming.
How many indexes are too many? There is no fixed number — it depends on the read-to-write ratio. Add them in response to measured slow queries and remove ones that are never used. Most databases can report index usage statistics.
What is a composite index? An index over several columns together. Column order matters: an index on (a, b) helps queries filtering on a, or a and b, but usually not b alone. Put the column you filter on most often first.
Why is my query still slow after adding an index? Common causes are a function wrapping the column, a leading wildcard in LIKE, a type mismatch, or the wrong column order in a composite index. Run EXPLAIN to see whether the index is actually being used.
Do indexes help with INSERT performance? No, they slow it down, because every index must be updated alongside the row. That is the core trade-off: faster reads in exchange for slower writes and more storage.