CODEWITHSUNDEEP
mysqlinnodbclustered-index
H.T.
H.T.

Reputation: 181

InnoDB: Find clustered index in existing table

Is there a way to explicitly determine the clustered index in an existing InnoDB table. I am aware of the rules for determining the clustered index that are listed in the documentation but I'd like to know if there is a way to explicitly check it in an existing table.

Upvotes: 2

Views: 504

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562861

As you have read, InnoDB tables always use the PRIMARY KEY or the first non-NULL UNIQUE KEY as their clustered index.

If the table has neither of those, it generates its own clustered index, and calls it 'GEN_CLUST_INDEX'.

mysql> create table test.testy ( i int ); -- no eligible clustered index

mysql> select t.name as table_name, i.name as index_name 
  from information_schema.innodb_sys_tables t 
  join information_schema.innodb_sys_indexes i using (table_id) 
  where i.name = 'GEN_CLUST_INDEX';
+------------+-----------------+
| table_name | index_name      |
+------------+-----------------+
| test/testy | GEN_CLUST_INDEX |
+------------+-----------------+

Upvotes: 2

Related Questions