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??.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
can we add cluster index on column (name) too?
No. Only one index is clustered.
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".
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
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.