MySQL Create Index

In MySQL, the index statement is used when we want to create an index to a table and retrieve data from the database more quickly than otherwise. The index cannot be seen as they are used to speed up the searches or queries.

Keep in mind that updating a table with indexes takes more time than updating a table without, so we need to create indexes for just columns that will be frequently searched against.

Syntax

For creating an index on a table.

Create Index in_name ON tablename (columnname);

For creating a unique index on a table.

Create Unique Index index_name ON tablename (columnname);

Example

CREATE INDEX ind_first ON emp (firstName);

This query creates an index named ind_first on the first name column in the emp table.

CREATE INDEX ind_name ON emp (firstName, lastName);

If we want to create combinations of indexes for columns, we can do that by separating by commas where we mention the column name.

To delete the created index in a table.

ALTER TABLE tablename DROP INDEX index_name;