Normally, you create all indexes on a table at the time the table
itself is created with CREATE
TABLE. See Section 13.1.20, “CREATE TABLE Syntax”. This
guideline is especially important for
InnoDB tables, where the primary key
determines the physical layout of rows in the data file.
CREATE INDEX enables you to add
indexes to existing tables.

An index specification of the form
(key_part1,
key_part2, ...) creates an
index with multiple key parts. Index key values are formed by
concatenating the values of the given key parts. For example
(col1, col2, col3) specifies a multiple-column
index with index keys consisting of values from
col1, col2, and
col3.

A key_part specification can end with
ASC or DESC to specify
whether index values are stored in ascending or descending order.
The default is ascending if no order specifier is given.
ASC and DESC are not
permitted for HASH indexes. As of MySQL 8.0.12,
ASC and DESC are not
permitted for SPATIAL indexes.

The following sections describe different aspects of the
CREATE INDEX statement:

Prefixes must be specified for
BLOB and
TEXT key parts. Additionally,
BLOB and
TEXT columns can be indexed
only for InnoDB,
MyISAM, and BLACKHOLE
tables.

Prefix limits are measured in bytes.
However, prefix lengths for index
specifications in CREATE
TABLE, ALTER TABLE,
and CREATE INDEX statements
are interpreted as number of characters for nonbinary string
types (CHAR,
VARCHAR,
TEXT) and number of bytes for
binary string types (BINARY,
VARBINARY,
BLOB). Take this into account
when specifying a prefix length for a nonbinary string
column that uses a multibyte character set.

For a nonunique index, either an error occurs (if strict SQL
mode is enabled), or the index length is reduced to lie
within the maximum column data type size and a warning is
produced (if strict SQL mode is not enabled).

For a unique index, an error occurs regardless of SQL mode
because reducing the index length might enable insertion of
nonunique entries that do not meet the specified uniqueness
requirement.

The statement shown here creates an index using the first 10
characters of the name column (assuming that
name has a nonbinary string type):

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

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

Functional Key Parts

A “normal” index indexes column values or prefixes
of column values. For example, in the following table, the index
entry for a given t1 row includes the full
col1 value and a prefix of the
col2 value consisting of its first 10
characters:

The virtual generated column itself requires no storage. The
index itself takes up storage space as any other index.

UNIQUE is supported for indexes that include
functional key parts. However, primary keys cannot include
functional key parts. A primary key requires the generated
column to be stored, but functional key parts are implemented as
virtual generated columns, not stored generated columns.

SPATIAL and FULLTEXT
indexes cannot have functional key parts.

If a table contains no primary key, InnoDB
automatically promotes the first UNIQUE NOT
NULL index to the primary key. This is not supported
for UNIQUE NOT NULL indexes that have
functional key parts.

Nonfunctional indexes raise a warning if there are duplicate
indexes. Indexes that contain functional key parts do not have
this feature.

To remove a column that is referenced by a functional key part,
the index must be removed first. Otherwise, an error occurs.

Although nonfunctional key parts support a prefix length
specification, this is not possible for functional key parts.
The solution is to use
SUBSTRING() (or
CAST(), as described later in
this section). For a functional key part containing the
SUBSTRING() function to be used
in a query, the WHERE clause must contain
SUBSTRING() with the same
arguments. In the following example, only the second
SELECT is able to use the index
because that is the only query in which the arguments to
SUBSTRING() match the index
specification:

Functional key parts enable indexing of values that cannot be
indexed otherwise, such as JSON
values. However, this must be done correctly to achieve the
desired effect. For example, this syntax does not work:

The hidden generated column is assigned the
VARCHAR(30) data type, which can
be indexed. But this approach produces a new issue when trying
to use the index:

CAST() returns a string with
the collation utf8mb4_0900_ai_ci (the
server default collation).

JSON_UNQUOTE() returns a
string with the collation utf8mb4_bin
(hard coded).

As a result, there is a collation mismatch between the indexed
expression in the preceding table definition and the
WHERE clause expression in the following
query:

SELECT * FROM employees WHERE data->>'$.name' = 'James';

The index is not used because the expressions in the query and
the index differ. To support this kind of scenario for
functional key parts, the optimizer automatically strips
CAST() when looking for an index
to use, but only if the collation of the
indexed expression matches that of the query expression. For an
index with a functional key part to be used, either of the
following two solutions work (although they differ somewhat in
effect):

Solution 1. Assign the indexed expression the same collation
as JSON_UNQUOTE():

Be aware that although the optimizer supports automatically
stripping CAST() with indexed
generated columns, the following approach does not work because
it produces a different result with and without an index
(Bug#27337092):

Unique Indexes

A UNIQUE index creates a constraint such that
all values in the index must be distinct. An error occurs if you
try to add a new row with a key value that matches an existing
row. If you specify a prefix value for a column in a
UNIQUE index, the column values must be
unique within the prefix length. A UNIQUE
index permits multiple NULL values for
columns that can contain NULL.

If a table has a PRIMARY KEY or
UNIQUE NOT NULL index that consists of a
single column that has an integer type, you can use
_rowid to refer to the indexed column in
SELECT statements, as follows:

_rowid refers to the PRIMARY
KEY column if there is a PRIMARY
KEY consisting of a single integer column. If
there is a PRIMARY KEY but it does not
consist of a single integer column,
_rowid cannot be used.

Otherwise, _rowid refers to the column in
the first UNIQUE NOT NULL index if that
index consists of a single integer column. If the first
UNIQUE NOT NULL index does not consist of
a single integer column, _rowid cannot be
used.

The index type for a non-SPATIAL index
depends on the storage engine. Currently, B-tree is used.

Permitted for a column that can have NULL
values only for InnoDB,
MyISAM, and
MEMORY tables.

Index Options

Following the key part list, index options can be given. An
index_option value can be any of the
following:

KEY_BLOCK_SIZE [=]
value

For MyISAM tables,
KEY_BLOCK_SIZE optionally specifies the
size in bytes to use for index key blocks. The value is
treated as a hint; a different size could be used if
necessary. A KEY_BLOCK_SIZE value
specified for an individual index definition overrides a
table-level KEY_BLOCK_SIZE value.

Table 13.1, “Index Types Per Storage Engine”
shows the permissible index type values supported by
different storage engines. Where multiple index types are
listed, the first one is the default when no index type
specifier is given. Storage engines not listed in the table
do not support an index_type
clause in index definitions.

If you specify an index type that is not valid for a given
storage engine, but another index type is available that the
engine can use without affecting query results, the engine
uses the available type. The parser recognizes
RTREE as a type name. As of MySQL 8.0.12,
this is permitted only for SPATIAL
indexes. Prior to 8.0.12, RTREE cannot be
specified for any storage engine.

BTREE indexes are implemented by the
NDB storage engine as T-tree
indexes.

Note

For indexes on NDB table
columns, the USING option can be
specified only for a unique index or primary key.
USING HASH prevents the creation of an
ordered index; otherwise, creating a unique index or
primary key on an NDB table
automatically results in the creation of both an ordered
index and a hash index, each of which indexes the same set
of columns.

For unique indexes that include one or more
NULL columns of an
NDB table, the hash index can
be used only to look up literal values, which means that
IS [NOT] NULL conditions require a full
scan of the table. One workaround is to make sure that a
unique index using one or more NULL
columns on such a table is always created in such a way
that it includes the ordered index; that is, avoid
employing USING HASH when creating the
index.

If you specify an index type that is not valid for a given
storage engine, but another index type is available that the
engine can use without affecting query results, the engine
uses the available type. The parser recognizes
RTREE as a type name, but currently this
cannot be specified for any storage engine.

Note

Use of the index_type option
before the ON
tbl_name clause is
deprecated; support for use of the option in this position
will be removed in a future MySQL release. If an
index_type option is given in
both the earlier and later positions, the final option
applies.

TYPE type_name
is recognized as a synonym for USING
type_name. However,
USING is the preferred form.

The following tables show index characteristics for the
storage engines that support the
index_type option.

If the page-full percentage for an index page falls below
the MERGE_THRESHOLD value when a row is
deleted or when a row is shortened by an update operation,
InnoDB attempts to merge the
index page with a neighboring index page. The default
MERGE_THRESHOLD value is 50, which is the
previously hardcoded value.

Specify index visibility. Indexes are visible by default. An
invisible index is not used by the optimizer. Specification
of index visibility applies to indexes other than primary
keys (either explicit or implicit). For more information,
see Section 8.3.12, “Invisible Indexes”.

Table Copying and Locking Options

ALGORITHM and LOCK clauses
may be given to influence the table copying method and level of
concurrency for reading and writing the table while its indexes
are being modified. They have the same meaning as for the
ALTER TABLE statement. For more
information, see Section 13.1.9, “ALTER TABLE Syntax”

User comments in this section are, as the name implies, provided by MySQL users.
The MySQL documentation team is not responsible for, nor do they endorse, any of
the information provided here.

Posted by
Rafael Leite
on
July 18, 2018

For those worried about locking the table for read/write operations, MySQL 5.6+ DOES NOT LOCK the table where the index is being created:

> The table remains available for read and write operations while the index is being created. The CREATE INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.