Can table have both primary & clustered index together

Question

Say we have a Student table in a MYSQL database and its schema is below. This table has around Million Records.

• ID (PK)
• Name
• Aadhaar_ID
• Address
• Admission_year

Can the above table have the following together at the same time ?

• Primary Index on the ID.
• Clustered Index on Name.

Since it's mandatory (for creation of Clustered-Index the data) for the data to be sorted, it's not possible to have the above 2 indexes together. We can only have either of two.

Answer 1

MySQL (InnoDB) rules:

• Only 1 clustered index.
• The PRIMARY KEY must be UNIQUE` and be composed of non-NULL columns.
• The PK determines the order of items in the data's B+Tree.
• It is OK for there to be gaps in any type of index, including the PK. (There are many cases where normal operations lead to gaps in an AUTO_INCREMENT id.)

Presumably, "name" cannot be unique?

So, do this:

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY(name, id),  -- to make access via `name` more efficient
INDEX(id)   -- sufficient to keep AUTO_INCREMENT happy