CREATE INDEX

Use this MySQL statement to add an index to a MySQL table after it has been created. This is an alias of relevant clauses of the MySQL statement ALTER TABLE that add indexes to 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|FULLTEXT|SPATIAL] INDEX index 
   [USING type|TYPE type] 
   [USING type|TYPE type] 
   ON table (column [(length)] [ASC|DESC], . . .)

Explanation

Use this statement to add an index to a table after it has been created. This is an alias of the ALTER TABLE statement that adds an index. You can add indexes only to MyISAM, InnoDB, and BDB types of tables. You can also create these tables with indexes, as shown under the CREATE TABLE statement.

To prevent duplicates, add the UNIQUE flag between the CREATE keyword and INDEX. Only columns with CHAR, TEXT, and VARCHAR datatypes of MyISAM tables can be indexed with FULLTEXT indexes.