CREATE INDEX Syntax
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
Summary: This is the Reference Manual for the MySQL Database System. It documents MySQL up to Version 5.0.4-beta, but is also applicable for older versions of the MySQL software (such as 3.23 or 4.0-production) because functional changes are indicated with reference to a version number.
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
In MySQL 3.22 or later, CREATE INDEX is mapped to an
ALTER TABLE statement to create indexes.
See Section 13.2.2, “ALTER TABLE Syntax”.
The CREATE INDEX statement doesn't do anything prior
to MySQL 3.22.
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)
a prefix consisting of the first length characters of each column
value. BLOB and TEXT 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 name column:
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!
Prefixes can be up to 255 bytes long (or 1000 bytes for MyISAM and InnoDB tables as of MySQL 4.1.2). Note that prefix limits
are measured in bytes, whereas the prefix length in CREATE INDEX statements is interpreted as number of characters. Take this into account
when specifying a prefix length for a column that uses a multi-byte
character set.
You can add an index on a column that can have NULL values only if you are using MySQL 3.23.2 or newer and are using the
MyISAM, InnoDB, or BDB table type. You can only add an
index on a BLOB or TEXT column if you are using
MySQL 3.23.2 or newer and are using the MyISAM or BDB table type, or MySQL 4.0.14 or newer and the InnoDB table type.
An index_col_name specification can 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.
From MySQL 4.1.0 on, some storage engines allow you to specify an index
type when creating an index. The syntax for the index_type specifier is USING type_name. The allowable type_name values 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.
| Storage Engine | Allowable Index Types |
MyISAM | BTREE |
InnoDB | BTREE |
MEMORY/HEAP | HASH, BTREE |
Example:
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);
TYPE type_name can be used as a synonym for USING type_name to specify an index type. However, USING is the preferred form.
Also, the index name that precedes the index type in the index
specification syntax is not optional with TYPE. This is because,
unlike USING, 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 storage engine,
but there is another index type available that the engine can use without
affecting query results, the engine uses the available type.
For more information about how MySQL uses indexes, see
Section 7.4.5, “How MySQL Uses Indexes”.
FULLTEXT indexes can index only CHAR, VARCHAR, and
TEXT columns, and only in MyISAM tables. FULLTEXT indexes
are available in MySQL 3.23.23 or later.
Section 12.6, “Full-Text Search Functions”.
SPATIAL indexes can index only spatial columns,
and only in MyISAM tables. SPATIAL indexes
are available in MySQL 4.1 or later. Spatial column types are described in
Chapter 18, Spatial Extensions in MySQL.