MySQL - Clustered Index

MySQL - Understanding Clustered Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Think of them as the index in the back of a book. Instead of reading the whole book to find a topic, you look it up in the index and go directly to the right page. MySQL indexes are primarily categorized into two types: clustered and non-clustered (also known as secondary indexes).

What is a Clustered Index?

A clustered index determines the physical order of data in a table. Because the data rows themselves can only be sorted in one physical order, a table can have only one clustered index.

The Phone Book Analogy: A printed phone book is a perfect real-world example of a clustered index. The entries (the data) are physically sorted by last name (the indexed column). When you look up ‘Smith’, you are directly navigating through the physically sorted data. You don’t look up ‘Smith’ in a separate index to find a page number; the data itself is the index.

Clustered Indexes in MySQL (InnoDB)

In MySQL, the concept of a clustered index is specific to the InnoDB storage engine, which is the default for modern MySQL versions. MyISAM, the older storage engine, does not use clustered indexes.

InnoDB does not have a separate CREATE CLUSTERED INDEX command. Instead, it automatically creates and manages the clustered index based on the following hierarchy:

1. Primary Key: If a table has a PRIMARY KEY, InnoDB uses it as the clustered index.
2. First UNIQUE NOT NULL Index: If there is no PRIMARY KEY, InnoDB uses the first UNIQUE index that has only NOT NULL columns as the clustered index.
3. Hidden System-Generated ID: If the table has neither a PRIMARY KEY nor a suitable UNIQUE index, InnoDB internally generates a hidden clustered index called GEN_CLUST_INDEX on a synthetic 6-byte column that contains row ID values.

Best Practice: Always explicitly define a PRIMARY KEY for your InnoDB tables. Relying on the implicit hidden index is not recommended as you have no control over it.

Example: Creating a Table with a Clustered Index

Let’s create a users table. By defining id as the PRIMARY KEY, we are implicitly telling InnoDB to use it as the clustered index.

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY (username)
) ENGINE=InnoDB;

In this table, the data on disk will be physically sorted according to the id column.

We can verify the indexes on the table using the SHOW INDEX command:

SHOW INDEX FROM users;

Output Analysis

The output will show two indexes:

*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: users
   Non_unique: 0
     Key_name: username
 Seq_in_index: 1
  Column_name: username
    Collation: A
  Cardinality: 0
    ...

The index with Key_name: PRIMARY is our clustered index. The username index is a non-clustered (or secondary) index.

Clustered vs. Non-Clustered Indexes

Feature	Clustered Index	Non-Clustered (Secondary) Index
Number per Table	Only one	Multiple
Data Storage	Sorts the physical data rows of the table. The leaf nodes of the index contain the actual data.	Has a separate structure from the data rows. The leaf nodes contain a pointer to the data row (the clustered key value).
Primary Purpose	Organizes the entire table for fast access.	Provides an alternative sort order for efficient searching on non-primary key columns.
Lookup Speed	Extremely fast for lookups and range queries on the clustered key, as the data is right there.	Requires an extra step: first, find the pointer in the secondary index, then use that pointer to look up the data in the clustered index.

Performance Implications and Best Practices

The choice of your clustered index (i.e., your primary key) has significant performance implications:

• Sequential vs. Random Keys: Always prefer a primary key that is narrow, unique, and ever-increasing (monotonic). An AUTO_INCREMENT integer is ideal. This allows new rows to be appended efficiently at the end of the table. Using a random value like a UUID (v4) as a primary key is terrible for performance, as it forces InnoDB to insert new rows into random locations in the middle of the table, causing page splits and heavy disk I/O.
• Secondary Index Size: Every secondary index entry stores a copy of the clustered key value to point back to the data row. Therefore, a large clustered key (e.g., a long string) will bloat the size of all your secondary indexes, wasting space and making them less efficient.
• Range Queries: Range queries on the clustered key column (e.g., WHERE id BETWEEN 100 AND 200) are very fast because the required data is physically located together on disk.