MySQL Clustered Index

Database systems allow you to create indexes to make it easy to lookup values and do database searches. Typically, they are B-trees that store key values for faster lookups. Clustered index are an index that enforce ordering of rows of the table physically.

Once a clustered index has been created, it will store all rows in the table as per key columns that are used to build clustered index. Each table can have only one clustered index since it will store the rows as per sorted order.

InnoDB require clustered indexes for running SELECT, INSERT, UPDATE and DELETE statements. Typically, the primary key of a table itself is used as a clustered index in MySQL. That is why you will find your table rows are generally sorted by primary key’s values.

In case your table does not have a primary key, then MySQL will look for the first field that has UNIQUE constraint, and use it as clustered index.

If your table doesn’t have any primary key or unique column, MySQL will create an internal cluster index GEN_CLUST_INDEX using a synthetic field row id.

All other indexes that you create for your table, are secondary indexes. Clustered indexes are always the primary index of your table.

Secondary indexes also contain primary key columns as well as the columns you defined for secondary index.

Here are the key advantages of clustered indexes:

• Maximize cache hits & reduce page transfers
• Great option for min, max, count queries

Here are the key disadvantages of clustered indexes:

• Creates page splits, data pages & index pages to maintain order
• Insert records are stored in non-sequential order
• May take long time to update records

In this article, we have learnt about clustered indexes in MySQL.

Also read:

How to Execute Stored Procedure in Python
How to Manage PostgreSQL Views
PostgreSQL Python Transaction
MySQL AND Operator with Examples
How to Enable Syntax Highlighting in Vim