CREATE INDEX

In MySQL, an index can be created on a table when the table is created with CREATE TABLE command. Otherwise, CREATE INDEX enables to add indexes to existing tables. A multiple-column index can be created using multiple columns.

The indexes are formed by concatenating the values of the given columns.

CREATE INDEX cannot be used to create a PRIMARY KEY.

Syntax:CREATE INDEX [index name] ON [table name]([column name]);

Arguments

Name	Description
index name	Name of the index.
table name	Name of the table.
column name	Name of the column.

Example:

Code:

CREATE  INDEX autid ON newauthor(aut_id);

Copy

Explanation:

The above MySQL statement will create an INDEX on ‘aut_id’ column for ‘newauthor’ table.

MySQL create UNIQUE INDEX

Create UNIQUE INDEX

Using CREATE UNIQUE INDEX, you can create an unique index in MySQL.

Example:

Code:

CREATE  UNIQUE INDEX newautid ON newauthor(aut_id);

Copy

Explanation:

The above MySQL statement will create an UNIQUE INDEX on ‘aut_id’ column for ‘newauthor’ table.

MySQL create UNIQUE INDEX with index type

Create UNIQUE INDEX with index type

In MySQL, you can specify the type of the INDEX with CREATE INDEX command to set a type for the index.

Example:

Code:

CREATE  UNIQUE INDEX newautid ON newauthor(aut_id) USING BTREE;

Copy

Explanation:

The above MySQL statement will create an INDEX on ‘aut_id’ column for ‘newauthor’ table by an INDEX TYPE BTREE.