SQL · Theory

Clustered Index

Theory

81/439

Clustered Index

In InnoDB the PRIMARY KEY is the clustered index: row data is stored in PK order. Secondary indexes store the PK to find the row. That is why a short integer PK is a common recommendation.

MySQL does not let you pick a random extra clustered index like some SQL Server setups. The PK is the cluster.

Trap — calling every INDEX ‘clustered’. Only the PK (InnoDB) is.

On the example next to this theory: Clustered Index — keys and constraints protect data integrity; indexes speed lookups.

Exam tip

InnoDB: PK = clustered index. Secondary indexes point at the PK.

Clustered Index — sample query

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);
INSERT INTO authors VALUES (1, 'a@x.com');
INSERT INTO books VALUES (1, 1, 'SQL Basics');
SELECT * FROM books;

Clustered Index — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefInnoDB clusters rows by PRIMARY KEY.
RulePrefer a stable integer PK.
TrapEvery index is not clustered.

CREATE TABLE authors ( id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL ); CREATE TABLE books ( id INTEGER PRIMARY KEY, author_id INTEGER NOT NULL, title TEXT NOT NULL, FOREIGN KEY (author_id) REFERENCES authors(id) ); INSERT INTO authors VALUES (1, 'a@x.com'); INSERT INTO books VALUES (1, 1, 'SQL Basics'); SELECT * FROM books;

Clustered Index

Clustered Index — sample query

Short notes

Questions

Clustered Index

Clustered Index — sample query

Short notes

Questions