If primary key is clustered index in a table then the other columns are eg any unique column is a table is non clustered?

Question

CREATE TABLE people(
personID int,
FirstName VARCHAR(255),
LastNanme VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255)
PRIMARY KEY (PERSONID)
);

Answer 1

Yes, a given table can only have one clustered index, and in InnoDB, it is always the primary key. All other indexes on that table are non-clustered.

The clustered index also stores all the columns of the table, including non-indexed columns, so in your example the FirstName, LastName, Address, and City are stored as fields in the leaf nodes of the clustered index.

A secondary index stores only the indexed columns, and the primary key of the table. So a search for all the columns of a table by a secondary index would execute two index searches: first to find the value you are searching in the secondary index, which would yield the primary key values in the rows where the value you were searching for occurs. Second, the primary key values would be used to search the clustered index, to get the other non-indexed columns. The InnoDB storage engine does both steps automatically, and delivers all the columns to you.