CREATE [UNIQUE|FULLTEXT] INDEX index_name [index_type]
ON tbl_name (index_col_name,...)

index_col_name:
col_name [(length)] [ASC | DESC]

The CREATE INDEX statement doesn't do anything in MySQL prior to Version 3.22. In Version 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See section 13.2.2 ALTER TABLE Syntax.

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See section 13.2.5 CREATE TABLE Syntax. CREATE INDEX allows you to add indexes to existing tables.

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index the first length bytes of each column value. (For BLOB and TEXT columns, a prefix length is required; length may be a value up to 255.) The statement shown here creates an index using the first 10 characters of the name column:

mysql> CREATE INDEX part_of_name ON customer (name(10));

Because most names usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column. Also, using partial columns for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations!

Note that you can add an index on a column that can have NULL values only if you are using MySQL Version 3.23.2 or newer and are using the MyISAM, InnoDB, or BDB table type. You can only add an index on a BLOB/TEXT column if you are using MySQL Version 3.23.2 or newer and are using the MyISAM or BDB table type, or MySQL Version 4.0.14 or newer and the InnoDB table type. For an index on a BLOB or TEXT column, a prefix length must always be specified.

An index_col_name specification may end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently they are parsed but ignored; index values are always stored in ascending order.

FULLTEXT indexes can index only CHAR, VARCHAR, and TEXT columns, and only in MyISAM tables. FULLTEXT indexes are available in MySQL Version 3.23.23 and later.

back to MySQL reference