MySQL CREATE UNIQUE INDEX Keyword
The MySQL CREATE UNIQUE INDEX keyword is used to create indexes on a table with unique name. Indexes are useful to retrieve data from the database more quickly and speeds up the search queries. Indexes are not visible to the end users. Along with this, updating a table with indexes takes more time as compared to a table without any indexes because indexes are updated. Therefore it is advised to create indexes only on frequently searched columns.
The INSERT and UPDATE statements take more time on tables having indexes, whereas the SELECT statements become fast on those tables. The reason is that while performing insert or update operation, the database update the indexes as well.
MySQL CREATE UNIQUE INDEX
Along with regular index, it is possible to create a unique index also on a table. Unique index is the same as the Primary key in MySQL. The unique index indicates that the combination of values in the indexed columns must be unique. Unique indexes are used for the maintenance of the integrity of the data present in the table as well as for the fast performance.
The syntax for using CREATE INDEX statement in MySQL is given below:
/* Allows only unique combination of values in the indexed columns */ CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
MySQL CREATE UNIQUE INDEX Example
To create a unique index on EmpID column of a Employee table, the query is mentioned below:
CREATE UNIQUE INDEX idx_empid ON Employee (EmpID);
To create a unique index on multiple columns (Name and EmpID) of a table (Employee), the query is given below:
CREATE UNIQUE INDEX idx_empid ON Employee (Name, EmpID);
❮ MySQL Keywords