MySQL CREATE INDEX

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

Example

Code:

CREATE  INDEX autid ON newauthor(aut_id);

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);

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;

Explanation

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