How to create a clustered index in MySQL?

2 years ago
Benjamin Taylor
2 minutes

In MySQL, you can use the following syntax to create a clustered index:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column_name)
) ENGINE=InnoDB;

In the above syntax, table_name refers to the name of the table on which the index is to be created, while column1, column2, etc. are the column names and corresponding data types of the table. When creating a table, a clustered index can be created by specifying a column as the primary key (PRIMARY KEY). Be sure to set the table’s engine to InnoDB, as only the InnoDB engine supports clustered indexes.

Clustered index is a way of physically storing data in the order of the index, so when creating a clustered index, one or more columns can be chosen as the order of the index. Primary key is typically used as clustered index because it is unique and does not allow null values.

Here is an example SQL query to create a table named employees and create a clustered index on the id column.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ENGINE=InnoDB;

In the example above, the id column is designated as the primary key and will automatically create a clustered index.

bannerAds