MySQL CREATE INDEX with Example

In SQL, CREATE INDEX command is used to create an index. The index is useful to speed up the execution of a SQL query that reads data and thus improves the performance of an application using a database.
 

Syntax for creating an ordinary index

The basic syntax for creating an index is as follows:

CREATE INDEX `index_name` ON `table`;

It is also possible to create an index on a single column by specifying the column on which the index should be applied:

CREATE INDEX `index_name` ON `table` (`column1`);

 

 
The example above will insert the index named “index_name” on the table named “table” only on the column “column1”. To insert an index on several columns it is possible to use the following syntax:

CREATE INDEX `index_name` ON `table` (`column1`, `column2`);

The example above allows you to insert an index on the 2 columns: column1 and column2.
 

Syntax for creating a unique index

A unique index allows you to specify one or more columns to contain unique values for each record. The database system will return an error if a query tries to insert data that will duplicate the unique key. To insert such an index you just need to execute a SQL query with the following syntax:

CREATE UNIQUE INDEX `index_name` ON `table` (`column1`);

In this example, a unique index will be created on the column named column1. This means that there cannot be more than one of the same values on two distinct records contained in this table.

It is also possible to create a unique index on 2 columns, using the following syntax:

CREATE UNIQUE INDEX `index_name` ON `table` (`column1`, `column2`);

 

 

Naming convention:

There is no specific naming convention for indexes, just suggestions from some developers and database administrators. Here is a list of suggested prefixes to use when naming an index:

• Prefix “PK_” for Primary Key
• Prefix “FK_” for Foreign Key
• Prefix “UK_” for Unique Key
• Prefix “UX_” for Unique Index
• Prefix “IX_” for every other IndeX

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More