Introduction to Indexes

An index is a separate data structure that the database maintains alongside a table, specifically to speed up retrieval of rows by the values in one or more columns. It does this at a cost: extra storage space to hold the index itself, and extra work on every INSERT, UPDATE, and DELETE to keep that index up to date. Deciding where to add an index is really a trade-off between faster reads and slower writes plus more storage — not a free win.

The library-index analogy

Think of a library with thousands of books shelved in the order they were acquired, with no card catalog. Finding a specific book by title means walking every shelf, checking each book, until it turns up — the library equivalent of a full table scan. A card catalog sorted by title lets a librarian jump almost directly to the right shelf. That catalog is not the books themselves; it is a separate, smaller structure that points to where each book actually lives. A database index works the same way: it is a compact, sorted structure that points back to the full row, so the database can locate matching rows without inspecting every single one.

Without an index: a full table scan

Without an index on email, finding one customer among millions means the database checks every row:

No index on email — the database must scan every row

SQL

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email       VARCHAR(255),
  full_name   VARCHAR(200)
);

-- With millions of rows and no index on email,
-- this has to check every row to find matches
SELECT * FROM customers WHERE email = 'alice@example.com';

Creating an index

Adding an index to speed up lookups by email

SQL

CREATE INDEX idx_customers_email ON customers (email);

-- The same query can now use the index to jump almost
-- directly to matching rows instead of scanning the table
SELECT * FROM customers WHERE email = 'alice@example.com';

From this point on, the database is free to use idx_customers_email whenever a query filters, joins, or sorts on the email column. The query itself does not change at all — indexing is a physical storage decision, not something reflected in SQL syntax.

What an index costs

Operation	Effect of adding an index
SELECT with a filter/join/sort on the indexed column	Usually much faster
INSERT	Slightly slower — the new row must also be added to the index
UPDATE of an indexed column	Slightly slower — the index entry must be relocated
DELETE	Slightly slower — the row must also be removed from the index
Disk / memory usage	Higher — the index is additional stored data

Primary keys are indexed automatically

Declaring a PRIMARY KEY (or, in most databases, a UNIQUE constraint) automatically creates a supporting index behind the scenes — there is no need to run a separate CREATE INDEX for it. This is why looking up a row by its primary key is fast by default, even on a huge table, without the developer having to think about indexing at all.

Indexes are one of the highest-leverage tools for query performance, but they are not something to add reflexively to every column. Later pages in this section cover how different kinds of indexes are structured (clustered vs non-clustered, single-column vs composite), how to read a query plan to see whether an index is actually being used, and practical guidelines for deciding which columns deserve one.

An index is a separate structure that lets the database find matching rows without scanning the whole table.
Indexes speed up reads but add overhead to writes and consume extra storage.
CREATE INDEX adds an index; the SQL used to query the table does not change.
Primary key columns (and typically UNIQUE columns) are indexed automatically.