DSA & System Design
All the basics

What is a database index?

An index is a sorted lookup list beside a table, so the database can jump to a row instead of reading all.

The edge tabs of a book: you jump straight to the section you want instead of reading every page.

Press Play to watch it run · 4 steps

Without an index the database reads every row. With one it jumps to the row, at the cost of space and slower writes.

Finding a row with and without an indexWithout an index the database reads every row. With one it jumps to the row, at the cost of space and slower writes.Your appIndexsorted emailsUsers table10 million rows

Tap any box to see what it is and what it talks to.

  • Service
  • Search index
  • Database
Open in playground
Text description
  1. The app asks for the user with one email address.
  2. With no index, the database reads row one, then row two.
  3. It keeps going through all 10 million rows to be sure.
  4. After a long wait, the one matching row is found.
  • An index is a sorted list that points to rows.
  • It turns reading every row into jumping to one.
  • It costs disk space and makes writes a little slower.

Reach for it when

When a slow query hunts one email among 10 million users, or sorts by a column. Tiny tables do not need one.

The catch

Every new user row must also update each index, so ten indexes on a busy table slow writes and eat disk.

Say it in an interview“I would index the columns used in frequent lookups, joins and sorts. I would accept slower writes and extra storage, and avoid indexing everything.”