MySQL Clustered Indexes

A clustered index is a special index in which the order of the key values ​​determines the physical order of the corresponding rows in the table. As an analogy, a clustered index is similar to a dictionary. The table of content in the dictionary are equivalent to the clustered index, and the table of content and words are sorted alphabetically.

Since the rows in a table can only be stored in one order, there can be at most one clustered index in a table.

MySQL clustered index on InnoDB table

Clustered indexes help optimize data operations on an InnoDB table, such as SELECT, INSERT, UPDATE and DELETE.

Each InnoDB table requires a clustered index.

If you define a primary key for an InnoDB table, MySQL uses the primary key as the clustered index.

If your table does not have a primary key, MySQL uses the first UNIQUE index with NOT NULL constraint as the clustered index.

If the InnoDB table does not have a primary key or a suitable UNIQUE index, MySQL internally generates a hidden clustered index named GEN_CLUST_INDEX, which is built on a column containing the ROW ID value.

All indexes except clustered indexes are nonclustered or secondary indexes. In an InnoDB database, each record in a secondary index contains the primary key columns for that row as well as the columns specified in the nonclustered index. MySQL uses this primary key value for row lookups in the clustered index.

Therefore, it is advantageous to have a shorter primary key, otherwise the secondary index would take up more space. Typically, auto-incrementing integer columns are used for primary key columns.

Conclusion

• Every InnoDB table has a clustered index.
• A clustered index determines the physical order of rows in a table.