CREATE INDEX Syntax
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX
length)] [ASC | DESC]
CREATE INDEX is mapped to an
TABLE statement to create indexes. See
Section 13.1.2, “
ALTER TABLE Syntax”. For more information about how
MySQL uses indexes, see Section 7.4.5, “How MySQL Uses Indexes”.
Normally, you create all indexes on a table at the time the
table itself is created with
See Section 13.1.5, “
CREATE TABLE Syntax”.
INDEX enables you to add indexes to existing tables.
A column list of the form
creates a multiple-column index. Index values are formed by
concatenating the values of the given columns.
columns, indexes can be created that use only part of a column,
syntax to specify an index prefix length. Index entries consist
of the first
length characters of
each column value for
VARCHAR columns, and the first
length bytes of each column value for
columns also can be indexed, but a prefix length
must be given.
The statement shown here creates an index using the first 10
characters of the
CREATE INDEX part_of_name ON customer (name(10));
If names in the column 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
Prefixes can be up to 1000 bytes long (767 bytes for
InnoDB tables). Note that prefix limits are
measured in bytes, whereas the prefix length in
INDEX statements is interpreted as number of
characters for non-binary data types (
TEXT). Take this
into account when specifying a prefix length for a column that
uses a multi-byte character set.
In MySQL 5.1:
You can add an index on a column that can have
NULL values only if you are using the
You can add an index on a
TEXT column only if you are using the
InnoDB storage engine.
index_col_name specification can
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
Some storage engines allow you to specify an index type when
creating an index. The syntax for the
index_type specifier is
supported by different storage engines are shown in the
following table. Where multiple index types are listed, the
first one is the default when no
index_type specifier is given.
||Allowable Index Types
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);
be used as a synonym for
to specify an
index type. However,
USING is the preferred
form. In addition, the index name that precedes the index type
in the index specification syntax is not optional with
TYPE is not a reserved word and thus is
interpreted as an index name.
If you specify an index type that is not legal for a given
storage engine, but there is another index type available that
the engine can use without affecting query results, the engine
uses the available type.
FULLTEXT indexes are supported only for
MyISAM tables and can include only
TEXT columns. See
Section 12.7, “Full-Text Search Functions”. A
PARSER clause can be specified to associate a parser
plugin with the index if full-text indexing and searching
operations need special handling. This clause is legal only for
FULLTEXT indexes. See
Section 27.2, “The MySQL Plugin Interface”, for details on creating plugins.
SPATIAL indexes are supported only for
MyISAM tables and can include only spatial
columns that are defined as
Chapter 18, Spatial Extensions, describes the spatial data