Summary: in this tutorial, you will learn about the MySQL clustered index and how clustered indexes are managed in InnoDB tables.
Introduction to the MySQL clustered index
Typically, an index is a separate data structure such as a B-Tree that stores the key values for faster lookups.
A clustered index, on the other hand, is the table. It is an index that enforces the ordering of the rows of the table physically.
Once a clustered index is created, all rows in the table will be stored according to the key columns used to create the clustered index.
Because a clustered index stores the rows in sorted order, each table has only one clustered index.
MySQL clustered indexes on InnoDB tables
Each InnoDB table requires a clustered index. The clustered index helps an InnoDB table optimize data manipulations such as SELECT, INSERT, UPDATE and DELETE.
When defining a primary key for an InnoDB table, MySQL uses the primary key as the clustered index.
If you do not have a primary key for a table, MySQL will search for the first UNIQUE index where all the key columns are NOT NULL and use this UNIQUE index as the clustered index.
In case the InnoDB table has no primary key or suitable UNIQUE index, MySQL internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains the row ID values.
As a result, each InnoDB table always has one and only one clustered index.
All indexes other than the clustered index are non-clustered indexes or secondary indexes. In InnoDB tables, each record in the secondary index contains the primary key columns for the row as well as the columns specified in the non-clustered index. MySQL uses this primary key value for the row lookups in the clustered index.
Therefore, it is advantageous to have a short primary key otherwise the secondary indexes will use more space. Typically, the auto-increment integer column is used for the primary key column.
Summary
- A cluster index is the table itself, which enforces the order of the rows in the table.
- An InnoDB table always has a clustered index.
