MySQL in a Nutshell

Name

CREATE INDEX

Synopsis

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index 
   [USING type|TYPE type] 
   [USING type|TYPE type] 
   ON table (column [(length)] [ASC|DESC],  . . . )

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. SPATIAL indexes can index spatial columns only in MyISAM tables. This is available starting with Version 4.1 of MySQL.

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. ...