Primary Index and Secondary Index

Primary vs Secondary Index: The Core Difference

In an InnoDB table, the clustered index is the B-tree whose leaf records contain the complete row data. A declared PRIMARY KEY becomes that clustered index. Every other index is a secondary index: its leaf records contain the secondary key and the row's primary-key columns.

Verified Orders Lab

DROP TABLE IF EXISTS orders_index_lab;
CREATE TABLE orders_index_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  notes VARCHAR(100)
) ENGINE = InnoDB;

INSERT INTO orders_index_lab VALUES
(1,101,'PAID',     '2026-08-01',1200.00,'School books'),
(2,101,'PENDING',  '2026-08-05', 500.00,'Stationery'),
(3,102,'PAID',     '2026-08-03', 750.00,'Uniform'),
(4,103,'CANCELLED','2026-08-04', 300.00,'Art kit'),
(5,101,'PAID',     '2026-08-10',1500.00,'Lab equipment'),
(6,102,'PENDING',  '2026-08-11', 900.00,'Sports kit'),
(7,104,'PAID',     '2026-08-12',2200.00,'Computer accessory'),
(8,101,'PAID',     '2026-08-14', 650.00,'Notebooks');

SELECT order_id, status, total_amount
FROM orders_index_lab
WHERE order_id = 5;

The result is deterministic. The tiny table demonstrates meaning, not speed: a cost-based optimizer can choose a scan for eight rows even when an index exists.

How InnoDB Chooses the Clustered Index

1. If the table declares a PRIMARY KEY, InnoDB uses it as the clustered index.
2. Without one, InnoDB chooses the first UNIQUE index whose key columns are all NOT NULL.
3. Without either, InnoDB creates a hidden GEN_CLUST_INDEX on a 6-byte synthetic row ID.

Every InnoDB table therefore has a clustered index, even if the DDL has no visible primary key. However, an application should normally define a meaningful, stable row identifier: the hidden row ID cannot be referenced by application SQL and gives the designer no deliberate identity contract.

-- Deliberate clustered key
CREATE TABLE students (
  student_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admission_no VARCHAR(20) NOT NULL UNIQUE,
  full_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

The auto-increment surrogate is one valid design, not a universal requirement. A stable natural key can be appropriate when it is genuinely short, immutable and authoritative.

The Two-Step Secondary-Index Lookup

CREATE INDEX idx_customer
ON orders_index_lab (customer_id);

SELECT order_id, order_date, total_amount, notes
FROM orders_index_lab
WHERE customer_id = 101
ORDER BY order_id;

Conceptually, InnoDB searches idx_customer, reads matching primary-key values 1, 2, 5 and 8, and uses those values to reach full clustered records. This second traversal is often called a bookmark lookup, row lookup or double read.

It is inaccurate to say that a secondary leaf stores an independent physical row address. In InnoDB it stores the primary-key columns. This design keeps references logically valid as pages split or rows move, but it makes primary-key width important.

When a Secondary Index Covers the Query

DROP INDEX idx_customer ON orders_index_lab;
CREATE INDEX idx_customer_status_date
ON orders_index_lab (customer_id, status, order_date);

SELECT order_id, status, order_date
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

This query can be covered because customer_id, status and order_date are explicit index parts, while order_id is the primary key stored in each InnoDB secondary record. If the query also selects notes, the index no longer contains every requested value and full clustered rows are needed.

EXPLAIN
SELECT order_id, status, order_date
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID';

In traditional EXPLAIN, Extra: Using index signals that the required column information is retrieved from the index tree. It does not mean merely “an index was chosen”; the key field answers that different question.

Design a Good InnoDB Primary Key

Do not choose a primary key only because it is “natural,” and do not choose a surrogate automatically. Compare business stability, data width, privacy exposure, distributed-ID generation, insert locality and referencing tables.

• INT/BIGINT AUTO_INCREMENT: compact and insertion-friendly, but coordinate generation and never treat the value as a business fact.
• UUID stored as text: easy across systems but wide; binary/time-ordered representations can reduce storage and locality penalties.
• Composite natural key: can enforce true identity, but its full width is repeated in each secondary record and referencing foreign key.

A UNIQUE secondary index can enforce a business identifier even when a compact surrogate remains the clustered primary key.

Inspect the Definitions and Verify the Plan

SHOW CREATE TABLE orders_index_lab;
SHOW INDEX FROM orders_index_lab;

EXPLAIN FORMAT=TREE
SELECT order_id, order_date
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID';

EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id, order_date
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID';

• SHOW INDEX confirms index name, uniqueness, key-part sequence and estimated cardinality.
• EXPLAIN shows the optimizer's estimated plan without running a normal SELECT result.
• EXPLAIN ANALYZE runs the SELECT and reports actual iterator timing, rows and loops in TREE format.

Common Mistakes and Faculty Checklist

• Calling every ordinary index “non-clustered” without naming the storage engine.
• Assuming physical row order is a guaranteed SELECT order. SQL output needs an explicit ORDER BY.
• Using a long mutable business string as the primary key without counting secondary-index cost.
• Creating separate single-column indexes for every predicate instead of examining composite workload patterns.
• Assuming any selected index removes the clustered lookup; verify whether the plan is covering.
• Judging an index on an eight-row lab rather than representative production-like data.
• Adding indexes but ignoring INSERT, UPDATE, DELETE, memory and storage overhead.

Practice: run the lab; compare primary-key and customer lookups; add and remove notes from the SELECT list; inspect Extra; then repeat with a representative data volume. Continue with composite index design and EXPLAIN query plans.

Official References

• MySQL 8.4: Clustered and Secondary Indexes
• MySQL 8.4: Primary Key Optimization
• MySQL 8.4: EXPLAIN Output Format

Cluster selection, secondary-record contents, primary-key width and covering-plan behavior were checked against the official MySQL 8.4 manual.