Skip to main content

What is an index in SQL? Why do we need indexes?

SQLJunior~1 min read
Suggest an edit

An index in SQL is a data structure that speeds up finding rows in a table. It works roughly like a book's table of contents: instead of flipping through every page, the database jumps straight to the right spot.

Why indexes matter

  • They speed up lookups (SELECT, WHERE, JOIN);
  • They make filtering and sorting faster;
  • They help enforce uniqueness constraints (PRIMARY KEY, UNIQUE) efficiently.

Example of creating an index

sql
CREATE INDEX idx_users_email ON users (email);

Now the query

sql
SELECT * FROM users WHERE email = 'user@mail.com';

runs faster, because the database won't scan the whole table, it'll find the record via the index.

The downside

Indexes take up space and slow down write operations (INSERT, UPDATE, DELETE), because the index has to be updated along with the table.

Short Answer

Interview ready
Premium

A concise answer to help you respond confidently on this topic during an interview.

Suggest an edit