Why should you care?

Range scans on order_id for “recent orders” are fast when order_id is PK — rows live together on disk pages.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE orders (
  order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  order_ref VARCHAR(32) NOT NULL,
  total_inr DECIMAL(12,2) NOT NULL,
  KEY idx_orders_customer (customer_id)
) ENGINE=InnoDB;
-- PK order_id = clustered index; secondary idx_orders_customer stores PK copies as pointers

What happened?

• InnoDB stores full row in PK B-tree.
• Secondary index leaves hold primary key values to find row.
• No PK means InnoDB picks hidden clustered key — always define explicit PK.

Practice next

1. SHOW CREATE TABLE orders\G — note PRIMARY KEY.
2. EXPLAIN SELECT * FROM orders WHERE order_id BETWEEN 100 AND 200;
3. Compare EXPLAIN on WHERE customer_id = 5 using secondary index + lookup.
4. Add composite PK (order_id, line_id) on order_items — clustering follows that order.
5. Measure INSERT speed UUID PK vs AUTO_INCREMENT (dev test).

Remember

Clustered index = PK row storage order. One per InnoDB table. Secondary indexes reference PK.

DataFlow orders PK

order_id AUTO_INCREMENT keeps clustered inserts sequential for nightly batch loads.

Outcome: Bulk import finishes faster than random UUID PK cluster.