MySQL CREATE INDEX

MySQL CREATE INDEX Statement

The CREATE INDEX statement in MySQL is used to create indexes on tables.

Indexes improve data retrieval speed by enabling faster search and query operations, although they are transparent to users.

Note: Updating a table with indexes takes more time compared to updating a table without them, as indexes also require updates. Therefore, create indexes only on columns that will be frequently used in searches.

CREATE INDEX Syntax

This statement creates an index on a table where duplicate values are permitted.

CREATE INDEX index_name
ON table_name (column1, column2, …);

CREATE UNIQUE INDEX Syntax

This statement creates a unique index on a table where duplicate values are not permitted.

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, …);

MySQL CREATE INDEX Example

The following SQL statement creates an index named “idx_lastname” on the “LastName” column in the “Persons” table:

CREATE INDEX idx_lastname
ON Persons (LastName);

To create an index on a combination of columns, list the column names within parentheses, separated by commas.

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

DROP INDEX Statement

The DROP INDEX statement is used to remove an index from a table.

ALTER TABLE table_name
DROP INDEX index_name;