MySQL Index – MySQL Clustered Index

By /
MySQL Index MySQL Clustered Index

MySQL uses a lot of techniques to optimize its performance, one such optimization is attained with the help of indexes. Indexes are used for quick retrieval of rows using one or more columns. If an index is not used, MySQL will start the iteration from the first row until it finds the required row.

It does not seem of any problem when working with a small database, let’s say 1000 rows. But when working with tens and thousands of records, it will cost resources and time. Indexes on the other hand if specified for the query in question, can be way faster as they won’t traverse all the columns sequentially. B-trees are used for storing indexes such as PRIMARY KEY, UNIQUE INDEX, and FULL TEXT.

Clustered Index and its Features

The clustered index is a special index created for each table present in InnoDB and it stores the row data. Generally, they are also referred to as a primary keys.

Features of clustered index are as follows:

  1. If a table has a primary key, InnoDB automatically uses the primary key column as a clustered index for that table.
  2. If in case the primary key is not defined in the table then InnoDB uses the first occurring NOT NULL UNIQUE column as a clustered index.
  3. If there is no PRIMARY KEY or UNIQUE and NOT NULL column present in the table, then InnoDB creates a hidden clustered index called GEN_CLUST_INDEX.

How to Create Clustered Indexes?

As we know clustered indexes are created by InnoDB itself when we create a primary key in a table. So let’s see how can we create a primary key:

CREATE TABLE tablename (
value1 datatype, 
value2 datatype, 
value3 datatype,
PRIMARY KEY(value1)          --clustered index 
);Code language: SQL (Structured Query Language) (sql)

You can execute the above query, using the MySQL command line. ‘value1’ will become the clustered for ‘tablename’ table. Clustered indexing is automatically done by the InnoDB, it cannot be created manually, in turn assigning a primary key to the table is a good practice.

What if you create a table where no column is UNIQUE or NOT NULL, basically, where you can’t assign any column as PRIMARY KEY. Well, in that case, make a column with auto-increment, as the auto-incremented value is UNIQUE and you constrain it as NOT NULL, InnoDB will take the auto-incremented value as a clustered index if PRIMARY KEY is not present.

How to Check Clustered Index for a Table?

To see the index of a particular table you can use the below query:

SHOW INDEX FROM table_name;Code language: SQL (Structured Query Language) (sql)

To view the indexed of all the tables belonging to a particular schema, you can use the below query:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'schema_name';Code language: SQL (Structured Query Language) (sql)

Clustered Index vs Secondary Index

A clustered index is an index declared as a primary key, whereas, any other index apart from the clustered index is said to be a secondary index. A clustered index is ordered on a non-key field whereas the secondary index is created in a non-key field and is unordered.

Conclusion

In this tutorial we learned what an index is, what is the use of indexing in SQL, and what is a clustered index and its features, we also learned how a clustered index is created using PRIMARY KEY, the other alternatives if PRIMARY KEY is not present. We also learn the difference between clustered index and secondary index. Finally, we learned how to check for indexes for a table.

For more details, you can refer to the official website for MySQL Clusters.