Search code examples
mysqlclustered-index

In MySQL, how can we tell if an index of a table is clustered or not?

In MySQL, how can we tell if an index of a table is clustered or not?

We can use show index from table-name to get information about the table's indexes. But I don't find it shows whether each index is clustered or nonclustered.

This is for the purpose of If a table in MySQL has any index, must the table have a clustered index?

Solution

  • In the default storage engine, InnoDB, the PRIMARY KEY index is always the clustered index.

    If you don't have a PRIMARY KEY, it's the first UNIQUE KEY index on non-null columns.

    If you don't have either a PRIMARY KEY or UNIQUE KEY on non-null columns, then InnoDB has a hidden clustered index. You can't make a query use this clustered index in this case.

    See https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

    If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

    MyISAM is the other common storage engine. MyISAM does not support a clustered index.