What is an index, and how does a primary key differ from a secondary index?

Question

IT Interview Practice · Accepted Answer

An index is a separate, sorted data structure (a **B-tree** in InnoDB) that lets MySQL find rows without scanning the whole table. Without one, a lookup reads every row (a **full table scan**); with one, it walks a tree in a few steps.

## Primary vs secondary

In InnoDB the table itself **is** the primary key index — a *clustered index*. Rows are physically stored in primary-key order, so the primary key lookup lands directly on the full row.

A **secondary index** is a separate B-tree keyed on other column(s). Its leaf nodes store the indexed value plus the **primary key**, so using a secondary index often costs a second lookup ("back to the clustered index") to fetch the rest of the row.

```sql
CREATE TABLE users (
  id     INT PRIMARY KEY,          -- clustered: rows stored in id order
  email  VARCHAR(255),
  city   VARCHAR(80)
);

CREATE INDEX idx_city ON users (city);   -- secondary B-tree on city

-- Fast: walks idx_city, then primary key for the rest of each row
SELECT * FROM users WHERE city = 'Hanoi';

-- Slow: no index on email -> full table scan
SELECT * FROM users WHERE email = 'a@b.com';
```

## Trade-off

Indexes speed up reads but every `INSERT`/`UPDATE`/`DELETE` must also update them, and they take disk. Index the columns you actually filter, join, or sort on — not every column.

## Why it matters

Indexes are the single biggest lever on query speed: the difference between reading 3 tree nodes and scanning a million rows. Knowing that InnoDB clusters on the primary key also explains why a short, monotonic key (like `AUTO_INCREMENT`) keeps inserts and every secondary index lean.

Primary vs secondary

Trade-off

Why it matters

Related questions