Database indexes

Play this lesson

Three seconds to find one player

Looking up a player by name in Shard Garden's table of 2 million rows takes 3 seconds.

PostgreSQL's documentation explains why: with no preparation, "the system would have to scan the entire table, row by row, to find all matching entries."

What an index is

A database index works like the index at the back of a book: a separate, sorted list that points to where each value lives. With one, PostgreSQL says, the database "might only have to walk a few levels deep into a search tree".

CREATE INDEX players_name_index ON players (name);

Quick check

Without an index, how does the database find a player by name?

  1. It reads the rows one by one until it has checked them all
  2. It jumps straight to the right row
  3. It asks the player
Show the answer

It reads the rows one by one until it has checked them all

Without a sorted structure to search, reading every row is the only way to be sure.

Indexes are not free

PostgreSQL again: "After an index is created, the system has to keep it synchronized with the table. This adds overhead to data manipulation operations."

Every new score or save also updates every index on that table. Indexes that queries never use should be removed.

Quick check

More indexes always make a database faster.

Show the answer

False

Indexes speed up the reads that use them and slow down every write. Each one must earn its place.

Ask the database

EXPLAIN shows how the database plans to run a query. Seq Scan means a sequential scan, reading every row in order. Index Scan means it used an index.

Remy looks at the plan of every slow query before adding an index.

Pick the index

top-scores.sql
SELECT * FROM scoresWHERE garden_id = 7ORDER BY points DESCLIMIT 10;

Priya flags this query as slow on a big table. Which index should Remy add?

  1. An index on garden_id and points
  2. An index on the player's email
  3. No index: buy more servers
Show the answer

An index on garden_id and points

The query filters by garden_id and sorts by points. An index on both lets the database jump to garden 7's rows already in order.

How sorted helps

Why does a sorted list make finding things so much faster? The next lesson lets you try it.

Bolt says: Next: find a player yourself, the way an index does.

Fact-checked 12 October 2026