DBMS · Indexes and performance

What is an index

Easyindexperformanceb-tree

Interview question

What is a database index and when should you use one?

How to answer

The short answer

An index is a separate structure that lets the database find rows without reading the whole table. It works like the index at the back of a book: instead of reading every page to find a word, you look the word up and jump to the page.

Most indexes are stored as a B-tree, a sorted tree that finds a value in a few steps even when the table holds millions of rows.

SQL
CREATE INDEX idx_orders_customer ON orders (customer_id);

SELECT * FROM orders WHERE customer_id = 42;   -- can use the index

Use an index on columns that are

  • Often used in WHERE conditions.
  • Used in JOIN conditions, such as .
  • Used in ORDER BY on large tables.

The cost of indexes

  • Every INSERT, UPDATE and DELETE must also update the index, so writes get slower.
  • Indexes take disk space.
  • A column with very few distinct values, such as a yes or no flag, gains little from a normal index.

When an index is not used: wrapping the column in a function, as in WHERE LOWER(email) = '[email protected]', usually stops the database using a plain index on email. Searching with a leading wildcard, as in LIKE '%son', has the same effect.

You can check whether a query uses an index with EXPLAIN. If it shows a full table scan on a large table, an index may help.

Interview shortcut

> An index speeds up reads and slows down writes, so add them for the queries you actually run.