CREATE INDEX - Creating UNIQUE indexes

These clauses of the CREATE INDEX statement can be used to create UNIQUE indexes in a MySQL table.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE UNIQUE INDEX index
ON table (column, . . .)

Explanation

After the INDEX keyword, the name of the index or key is given. This name can be the same as one of the columns indexed, or a totally new name.

You can specify the type of index with the USING keyword. For MyISAM and InnoDB tables, BTREE is the default and only choice currently. The RTREE type will be available as of version 5 of MySQL. The TYPE keyword is an alias for USING.

For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.

Although there is an ASC option for sorting indexes in ascending order and a DESC option for sorting in descending order, these are for a future release of MySQL. All indexes are currently sorted in ascending order. Additional columns for indexing may be given within the parentheses.

Examples

CREATE UNIQUE INDEX client_name
   ON clients (client_lastname, client_firstname(4), rec_date);

In this example, an index is created called client_name. It is based on the last names of clients, the first four letters of their first names, and the dates that the records were created. This index is based on it being unlikely that a record would be created on the same day for two people with the same last name and a first name starting with the same four letters.

To see the indexes that have been created for a table, use the SHOW INDEX statement. To remove an index, use the DROP INDEX statement.