MySQL Create index

An index creation helps to make a row of the table unique. The index operates and handles table data quickly. MySQL index requires NOT NULL column constraint. The index column primarily assigns with an integer data type. We can create an index in an existing table or a new table. Mostly, the index column assigns the left-most column of the table. The index assigns for either single or multiple columns.

Syntax

The create index syntax in a new table shows below. MySQL index creates using the "INDEX" keyword.

Create table table_name (
Column1 INT NOT NULL PRIMARY KEY,
Column2 INT NOT NULL,
Column3 INT NOT NULL,
Column4 varchar(45),
INDEX (Column1, Column2));

The syntax of a create index with an existing table shows below:

CREATE INDEX index_name ON table_name (
Column1, column2… columnN);

Examples of the create index

Let us understand how to create an index in MySQL with the help of an example.

Example 1: Create an index with the new table.

Execute the below query to create the table and index column. Here, the index assigns to multiple columns. You can apply index on either single column or multiple columns.

mysql> create table index_table(
-> roll_number INT NOT NULL PRIMARY KEY,
-> number INT NOT NULL,
-> mark INT NOT NULL,
-> name varchar(45),
-> INDEX (roll_number, number));

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows the index column of the table. This output displays index type as per storage index.

2) Example: Create index with an existing table

Execute the below query to create an index column in an existing table. Here, the index assigns to a single column. You can apply index on either single column or multiple columns.

mysql> create index mark on index_table(mark);

OUTPUT

Execute the following query to show the index of the table.

mysql> show index from index_table;

The above output image shows the index column of the table. This output display index type as per storage index.

Storage index and index type

MySQL creates a B-Tree index type by default. This index type supports all storage engines. If you want to change the index type, then you need to specify the storage engine. MySQL table specifies storage engine and index type as per application requirement. The following table shows the storage engine and its index type.

Storage Engine	MySQL Index Type
InnoDB storage engine	This storage engine supports to B-Tree index type.
MyISAM storage engine	This engine allows only B-Tree index type.
MEMORY/HEAP	These storage engines allow the HASH index and B-Tree index.