Clustered vs Non-Clustered Index — SQL Technical Interview Questions & Answers

Clustered vs Non-Clustered Index

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.

Technical Interview Tip: PK clustered. Other indexes secondary. They store the PK.

Code Examples for Clustered vs Non-Clustered Index

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.

How to Explain Clustered vs Non-Clustered Index to an Interviewer

Secondary index pointer in InnoDB?

The PRIMARY KEY value