0
create table t1 (id int primary key auto_increment, name varchar(20))

In the above table id is primary key and by default cluster index is added to primary key column.

My question is, can we add cluster index on column (name) too??.

0
0

can we add cluster index on column (name) too?

No. Only one index is clustered.

For MySQL:

  1. If primary key exists it is clustered.
  2. If no primary index but unique indexes exists then first unique index is clustered.
  3. If no unique indexes exists then the index by inner record number (hidden, unaccessable) is clustered one.
2
  • can we delete cluster index from primary key column? Jan 30 '20 at 13:36
  • PK is CI itself. Delete CI == drop PK. And some other index will be converted to a clusterув index-according to the priorities described above.
    – Akina
    Jan 30 '20 at 18:47
0

What is the use case? Normalization? That is a table with 2 columns, a 'name' and an 'id' for it? Then this is the optimal pattern:

CREATE TABLE Names (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(44) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
) ENGINE=InnoDB;

This effectively gives you two BTrees, each with both columns in it, and each constrained to have one of the columns be UNIQUE. Phrased differently, one provides the optimal lookup from id to name; the other provides the optimal lookup the other way.

One could play semantic games and say that the UNIQUE(name) provides a second "clustered" index because id is implicitly included in that BTree.

Don't focus on the term "clustered"; let's drop down into the implementation and see if it gives you what you really want, namely "efficiency".

Focus on how efficient the index you are using is (or is not). When doing SELECT name FROM Names WHERE id = 123, the BTree created by the PRIMARY KEY will be used very efficiently -- drill down the tree to locate the name in the leaf node for "123".

Similarly for SELECT id FROM Names WHERE name = 'Smith', the BTree created by UNIQUE(name) will be used. Since the PK is implicitly included in InnoDB secondary indexes; this is effectively a BTree on (name, id) (plus a uniqueness constraint on name). Again, the lookup will be a very efficient drill-down into the BTree, landing on the row in the leaf node that contains the pair (name, id).

For all practical purposes, Names has two "clustered indexes". But, but understanding how BTrees work and how efficient the BTrees work for these queries, we avoid the word "clustered" and focus on performance.

2
  • but by default cluster index is added to primary key and we can't add more than one cluter index on a table, so how UNIQUE(name) will get second cluster index?? Feb 3 '20 at 12:45
  • @tajamulhussain - I added a few paragraphs to shift the focus from "clustered" to "efficient".
    – Rick James
    Feb 3 '20 at 16:11
-2

Note:

  • Yes, You can add cluster index on column (name) But it's once time because in one table have only one cluster Index in SQLServer.
3
  • The questions was asking if they can add a second cluster index. Jan 30 '20 at 14:36
  • 1
    The question is about MySQL, not SQL Server. Jan 30 '20 at 18:36
  • @Colin'tHart @ AnthonyGenovese okay, no they can't add second cluster index in one table in MySQL or SQLServer Jan 31 '20 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.