Definition
- Automatic indexing – Declaring a
PRIMARY KEYcreates a B-tree index without you asking, so a primary-key lookup is never a full table scan. - Unique constraint – The primary key is guaranteed unique and
NOT NULL, so the optimizer knows at most one row can match and stops immediately – a unique scan. - B-tree depth – High fanout means even hundreds of millions of rows form a tree only three or four levels deep, so every lookup costs the same few page reads.
- Clustered index – In InnoDB and SQL Server the primary key is the clustered index, so the leaf node contains the full row with no secondary hop.
- Sorted order – Because the key is already sorted, range queries and
ORDER BYon the primary key read straight from the index with no separate sort step.
At the National Bank of India, the account number is the primary key. Finding Rajesh by account SAV2001 is like using a perfectly organized vault: the account numbers are the physical filing order (clustered), each number appears exactly once (unique), and there is a quick multi-level directory to jump to the right shelf (B-tree). The clerk does three jumps, opens one folder, and because account numbers only ever appear once, the clerk stops immediately. And because the folders are already in number order, asking for "accounts SAV2001 through SAV2005" needs no re-sorting.
Understanding the Concepts
Why does a primary key automatically create an index?
When you declare a PRIMARY KEY, the database builds a B-tree index on it without you writing a separate CREATE INDEX. This means a primary-key lookup is always a tree traversal, never a full table scan.
-- National Bank of India: PRIMARY KEY creates the index automatically
CREATE TABLE accounts (
account_id VARCHAR(20) PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
balance DECIMAL(15,2) NOT NULL DEFAULT 0.00
);
-- This lookup uses the automatic B-tree index
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE account_id = 'SAV2001';
Index Scan using accounts_pkey on accounts
Index Cond: (account_id = 'SAV2001')
Rows: 1
Execution Time: 0.03 ms
Why does the unique constraint make lookups faster?
A primary key is guaranteed unique and not null, so the optimizer knows a lookup returns at most one row. It can use a unique scan, the cheapest kind of index access, and stop the moment it finds the match.
-- Unique scan: at most 1 row, engine stops immediately
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE account_id = 'SAV2001';
Index Scan using accounts_pkey on accounts
Index Cond: (account_id = 'SAV2001')
Rows: 1 (engine stops after finding it)
Compare this to a non-unique index where the engine must continue scanning to check for more matches.
-- Non-unique index: engine must keep scanning for more matches
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE branch_code = 'MUM001';
Index Scan using ix_accounts_branch on accounts
Index Cond: (branch_code = 'MUM001')
Rows: 5000 (engine scans until no more matches)
Why does B-tree depth make primary-key lookups predictable?
A B-tree is balanced with very high fanout. A single 8 KB page can hold hundreds of keys, so even two hundred million rows form a tree only three or four levels deep. Every primary-key lookup costs the same small number of page reads.
B-tree depth for National Bank of India:
accounts (10M rows) -> 3 levels = 3 page reads
transactions (200M rows) -> 4 levels = 4 page reads
audit_log (1B rows) -> 4 levels = 4 page reads
Every PK lookup: 3-4 page reads, regardless of table size
-- 200 million transactions: still only 4 page reads
SELECT * FROM transactions WHERE txn_id = 987654321;
-- Execution Time: 0.05 ms (not 200 million rows)
How does the clustered index eliminate the bookmark lookup?
In InnoDB and SQL Server, the primary key is the clustered index. The leaf pages of the B-tree contain the actual row data, so reaching the key gives you the full row with no extra hop.
-- Clustered PK lookup: one traversal, full row returned
SELECT * FROM accounts WHERE account_id = 'SAV2001';
-- Path: root -> internal -> leaf (leaf IS the row)
-- Cost: 3 page reads, 0 bookmark lookups
-- Non-clustered lookup: two traversals
SELECT * FROM accounts WHERE email = 'rajesh@bank.in';
-- Path 1: email index -> find account_id = 'SAV2001'
-- Path 2: clustered index -> fetch full row
-- Cost: 6 page reads (3 + 3)
// com.bank.service.AccountLookupService
package com.bank.service;
import java.math.BigDecimal;
import java.sql.*;
public class AccountLookupService {
/**
* Primary-key lookup: uses the clustered index.
* One B-tree traversal, full row returned, no bookmark lookup.
*/
public BigDecimal getBalance(Connection conn, String accountId)
throws SQLException {
String sql = "SELECT balance FROM accounts WHERE account_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId); // match VARCHAR type exactly
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getBigDecimal("balance");
}
throw new IllegalArgumentException(
"Account not found: " + accountId);
}
}
}
}
How does the sorted order eliminate the sort step?
Because the primary-key index is sorted, range queries and ORDER BY on the primary key read straight from the index with no separate sort operation.
-- Range query on primary key: no sort needed
SELECT * FROM accounts
WHERE account_id BETWEEN 'SAV2000' AND 'SAV2100'
ORDER BY account_id;
-- Plan: Index Scan using accounts_pkey (no Sort node)
-- Descending order: just read the index backward
SELECT * FROM transactions
WHERE txn_id > 900000000
ORDER BY txn_id DESC
LIMIT 10;
-- Plan: Index Scan Backward using transactions_pkey (no Sort node)
How does a primary-key lookup compare to a secondary index lookup?
| Aspect | Primary-Key Lookup | Secondary Index Lookup |
|---|---|---|
| B-tree traversals | 1 | 2 (secondary + clustered) |
| Bookmark lookup | None | Yes (must fetch from clustered) |
| Uniqueness guarantee | Always | Only if UNIQUE |
| Sort elimination | Yes (key is sorted) | Only if sort matches index |
| Page reads (typical) | 3-4 | 6-8 |
What about foreign key references?
Foreign keys reference the primary key, and because the primary key is indexed and unique, the database can efficiently verify referential integrity on every insert and update.
-- transactions.account_id references accounts.account_id (PK)
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(20) NOT NULL
REFERENCES accounts(account_id),
amount DECIMAL(15,2) NOT NULL
);
-- On INSERT, the DB does a PK lookup on accounts to verify
-- This is fast because the PK is indexed
INSERT INTO transactions (account_id, amount)
VALUES ('SAV2001', 15000.00);
-- Internally: verify accounts.account_id = 'SAV2001' exists (PK seek)
What should a senior engineer know?
In InnoDB the primary key is embedded in every secondary index as the row pointer. A bloated primary key slows down every other lookup.
-- Bad: 36-byte UUID string as PK
-- Every secondary index entry carries 36 bytes for the pointer
CREATE TABLE payments_bad (
id CHAR(36) PRIMARY KEY -- 36 bytes per secondary index entry
);
-- Good: 8-byte BIGINT as PK
-- Every secondary index entry carries only 8 bytes
CREATE TABLE payments_good (
id BIGSERIAL PRIMARY KEY -- 8 bytes per secondary index entry
);
What are common interview follow-up questions?
- What is the difference between a primary key and a unique index?
- Why does InnoDB embed the primary key in every secondary index?
- How does a covering index avoid the bookmark lookup that non-PK lookups require?
- What happens when the primary key is a random UUID instead of an auto-increment?
Visual Overview
┌─────────────────────────────────────────────────┐ │ Why Primary-Key Searches Are Fast │ ├─────────────────────────────────────────────────┤ │ │ │ 5 Reasons Combined: │ │ │ │ 1. Automatic B-tree index │ │ PRIMARY KEY ──► B-tree created for free │ │ │ │ 2. Unique constraint │ │ At most 1 row ──► stop after first match │ │ │ │ 3. Shallow tree (high fanout) │ │ 200M rows ──► 4 levels ──► 4 page reads │ │ │ │ 4. Clustered index (InnoDB) │ │ Leaf = full row ──► no bookmark lookup │ │ │ │ 5. Sorted order │ │ Range/ORDER BY ──► no extra sort step │ │ │ │ PK Lookup Path (InnoDB): │ │ ┌──────┐ ┌──────────┐ ┌───────────────┐ │ │ │ Root │─►│ Internal │─►│ Leaf (= ROW) │ │ │ └──────┘ └──────────┘ └───────────────┘ │ │ 3 page reads, 0 extra hops, 1 row returned │ │ │ │ Secondary Index Lookup Path: │ │ ┌──────┐ ┌──────┐ ┌─────────────┐ │ │ │ Root │─►│ Leaf │─►│ PK pointer │ │ │ └──────┘ └──────┘ └──────┬──────┘ │ │ ▼ │ │ ┌──────────────┐ │ │ │ Clustered │ │ │ │ Index Lookup │ │ │ └──────────────┘ │ │ 6 page reads, 1 extra hop │ └─────────────────────────────────────────────────┘
Key Takeaway
Primary-key searches are fast because the primary key gives you five advantages at once, all for free. First, declaring a primary key automatically builds a B-tree index on it, so a lookup is a logarithmic tree traversal, not a full table scan. Second, the primary key is unique and not null, so the optimizer knows at most one row can match and stops the instant it finds it, which is the fastest kind of index access, a unique scan. Third, the B-tree is balanced with very high fanout, so even two hundred million rows form a tree only three or four levels deep, and every lookup costs the same tiny, predictable number of page reads.
Fourth, in clustered engines like InnoDB and SQL Server the primary key is the clustered index, which means the row data physically lives in the leaf of the primary-key tree. Reaching the key hands back the whole row with no secondary bookmark lookup, whereas a search by a non-key column needs a secondary index seek and then a second traversal into the clustered index. Fifth, because the key is already sorted, range queries and ordered queries on the primary key read straight from the index and skip the sort step entirely.
At the National Bank of India the core banking screen loads accounts by account number millions of times a day, and it is effectively instant because that path is a unique, clustered, sorted B-tree lookup. When Rajesh instead looked up an account by email, which is not the primary key, the plan showed two traversals – the secondary email index to get the id, then a bookmark lookup into the clustered index for the row – which is strictly more work and made the contrast obvious.
The nuance Pankaj always adds is that because InnoDB embeds the primary key inside every secondary index as its row pointer, a bloated primary key quietly slows down every other lookup in the table. That is why the team deliberately kept the account identifier compact, so the primary key stayed fast and every secondary index stayed lean.
Remember one sentence: the primary key gives you a unique, sorted, clustered B-tree for free, so lookups are logarithmic, stop at the first hit, and need no extra sort.
---