7.26 CREATE INDEX syntax

CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length)],... )

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

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. CREATE TABLE. 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. (On BLOB and TEXT columns the length is required). The statement shown below creates an index using the first 10 characters of the name column:

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

Since 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 only add a index on a column that can have NULL values or on a BLOB/TEXT column if you are useing MySQL version 3.23.2 or newer and are using the MyISAM table type.

For more information about how MySQL uses indexes, see MySQL indexes.