-2

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Aditya Goel
  • 201
  • 1
  • 15
  • The documentation justifies your claims where? How does "mandatory for the data to be sorted" imply "it's not possible to have the above 2 indexes together"? Your post/reasoning is not clear. What happened when you tried? [mre] – philipxy Nov 28 '22 at 04:23
  • @philipxy : Imagine we already have ID as sorted column in our aforementioned table. Therefore, we would have the Primary-Index (can be Sparse-type of Index) created on the same. Now, Say I also want to create a Clustered-Index on the Aadhaar_id column. Since the values in aadhaar_id field are present in random order, Is it possible to have the Clustered Index on the aadhaar_id field ? – Aditya Goel Nov 28 '22 at 06:35
  • I understand that, Whenever we apply clustered indexing in a table, it will perform sorting in that table only, therefore we can create only one clustered index in a table like primary-key. Clustered index is as same as dictionary where the data is arranged by alphabetical order. – Aditya Goel Nov 28 '22 at 06:42
  • Please clarify via edits, not comments. PS You still aren't referencing the manual, which an answer or anyone must for a justified answer. Also you are asking a different question in a comment. Please ask 1 question. – philipxy Nov 28 '22 at 08:38

1 Answers1

0

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
Rick James
  • 135,179
  • 13
  • 127
  • 222