Describe how InnoDB’s clustered index structure affects row storage and lookups.

Primary key lookups are extremely fast because the row is found directly in the index.

Range queries using the primary key (e.g., WHERE id BETWEEN 10 AND 20) are efficient due to physical ordering.

Sequential scans on primary key order require fewer random disk reads.

Secondary (non-clustered) indexes store the indexed column plus the PRIMARY KEY value.

To fetch a row via a secondary index, InnoDB must:

• Search the secondary index → retrieve primary key

• Use the primary key to search the clustered index → fetch full row

This second step is known as a back to the primary index lookup.

Primary key choice affects physical storage and performance.

Large or random primary keys (e.g., UUID) cause fragmentation and poor locality.

Auto-increment primary keys insert rows at the end, improving write performance.

Secondary indexes become larger when the primary key is large.

Fast primary-key lookups.

Efficient range scans.

Better I/O performance due to locality of data.

Makes covering indexes even faster when all required data is in the secondary index.