MySQL - Clustered Index

Indexes in MySQL are used to retrieve the data much faster from the database. We (users) cannot see the indexes, but they work behind to speed up searches and queries. They are categorized into two types: clustered and non-clustered indexes.

A clustered index can sort the data in a table manually. When data is inserted into the column with clustered index, the records are automatically sorted in a specified order. So, each table can only have one clustered index since it determines the sort order of the data.

MySQL Clustered Indexes

MySQL database does not have separate provisions for Clustered indexes. They are automatically created when PRIMARY KEY is defined on a table. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is treated as a Clustered index.

If a table has no Primary Key or UNIQUE index, MySQL will internally create a hidden clustered index named GEN_CLUST_INDEX on a column that contains the row ID values.

The rows of a table are ordered using row ID values generated by InnoDB.

Example

Let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE  INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (20, 2),
   PRIMARY KEY(ID)
);

Now, we will insert some values in to the above created table using the INSERT statement −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2500),
(4, 'Chaitali', '26', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'MP', 9000),
(7, 'Muffy', '24', 'Indore', 5500);

The table will be created as follows −

Using the following query, we can list all the indexes created on the CUSTOMERS table −

SHOW INDEX FROM CUSTOMERS\G

Output

As we can see in the output below, the PRIMARY KEY is created on the ID column of CUSTOMERS table.

*************************** 1. row ***************************
        Table: customers
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)