Name
CREATE INDEX
Synopsis
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEXindex[USINGtype|TYPEtype] [USINGtype|TYPEtype] ONtable(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 clause 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 in the CREATE TABLE statement later in this chapter.
To prevent duplicates, add the UNIQUE flag between the
CREATE keyword and INDEX. Only
columns with CHAR, TEXT, and
VARCHAR data types of MyISAM tables can be indexed
with FULLTEXT indexes.
Creating UNIQUE indexes
CREATE UNIQUE INDEXindexONtable(column, ...)
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, but
RTREE is also available as of version 5.0 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 ...