SQL · Theory

Clustered vs Non-Clustered Index

Theory

82/439

Clustered vs Non-Clustered Index

Clustered (InnoDB PK): the table IS the index. Non-clustered (secondary): a separate structure that points to the PK, then to the row.

Lookup by PK is one jump. Lookup by email (secondary) is index → PK → row. Still fast vs a full scan.

Trap — SQL Server textbook answers copied 1:1 without saying InnoDB PK is the cluster.

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

Exam tip

PK clustered. Other indexes secondary. They store the PK.

Clustered vs Non-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 vs Non-Clustered Index — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefClustered = InnoDB PK. Secondary = non-clustered.
RuleSecondary index stores the PK as the pointer.
TrapCopying SQL Server clustered-index stories blindly.

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 vs Non-Clustered Index

Clustered vs Non-Clustered Index — sample query

Short notes

Questions

Clustered vs Non-Clustered Index

Clustered vs Non-Clustered Index — sample query

Short notes

Questions