MySQL CREATE INDEX
MySQL CREATE INDEX Statement
Table indexes are created using the CREATE INDEX statement.
Data can be retrieved from the database more quickly when using indexes. The indexes are only there to speed up searches and queries; users cannot see them.
Note: Because the indexes also need to be updated, updating a table with indexes takes longer than updating a table without. Therefore, only build indexes on columns that will be used for frequent searches.
CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
MySQL CREATE INDEX Example
An index called “idx_lastname” is created on the “LastName” column in the “Persons” database by the SQL statement that follows:
CREATE INDEX idx_lastname
ON Persons (LastName);
You can list the column names in the parenthesis, separated by commas, if you wish to establish an index on a subset of the columns:
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
DROP INDEX Statement
A table’s index can be removed using the DROP INDEX statement.
ALTER TABLE table_name
DROP INDEX index_name;
