What Is a Database Index and How Does It Speed Up Queries?

A database index is a separate sorted data structure that lets the database find rows without scanning the whole table. Learn how indexes work and their tradeoffs.

Published September 19, 2026

A database index is a sorted lookup structure (usually a B-tree) on one or more columns that lets the database find rows without scanning the whole table. It speeds up reads but slows down writes and uses disk space.

The concept

Without an index the database performs a full table scan, checking every row. With an index on the filtered column it walks a small tree and jumps straight to the matching rows, like using a book's index.

Every INSERT, UPDATE and DELETE must also update each index, so index the columns you filter, join and sort on, not everything.

Scan vs index lookup

SELECT * FROM users WHERE email = 'a@x.com';
no index → scanned 1,000,000 rows
CREATE INDEX idx_users_email ON users(email);
with index → read 1 row

Practical examples

Create and check

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Composite index order matters

Works for filters on (a), or (a, b), but not b alone.

CREATE INDEX idx_ab ON t (a, b);

Unique index

CREATE UNIQUE INDEX uq_users_email ON users (email);

Index killer

SELECT * FROM users WHERE LOWER(email) = 'a@x.com'; -- function on column ignores the index

Common mistakes

  • Indexing every column and slowing every write.
  • Wrapping the indexed column in a function or leading wildcard LIKE '%x'.
  • Wrong column order in composite indexes.
  • Never checking EXPLAIN.

Key takeaways

  • Indexes trade write speed and space for read speed.
  • Index columns used in WHERE, JOIN and ORDER BY.
  • Verify with EXPLAIN.

FAQ

Does a primary key have an index?

Yes, automatically, and unique constraints do too.

How many indexes is too many?

When writes slow noticeably or indexes are unused; drop those you cannot justify.

More SQL articles