MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
CREATE [ONLINE | OFFLINE] [UNIQUE | FULLTEXT | SPATIAL] INDEXindex_name
[index_type
] ONtbl_name
(key_part
,...) [index_option
] [algorithm_option
|lock_option
] ...key_part
:col_name
[(length
)] [ASC | DESC]index_option
: { KEY_BLOCK_SIZE [=]value
|index_type
| WITH PARSERparser_name
| COMMENT 'string
' }index_type
: USING {BTREE | HASH}algorithm_option
: ALGORITHM [=] {DEFAULT | INPLACE | COPY}lock_option
: LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
Normally, you create all indexes on a table at the time the table
itself is created with CREATE
TABLE
. See Section 13.1.17, “CREATE TABLE Statement”. 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.
CREATE INDEX
is mapped to an
ALTER TABLE
statement to create
indexes. See Section 13.1.7, “ALTER TABLE Statement”.
CREATE INDEX
cannot be used to
create a PRIMARY KEY
; use
ALTER TABLE
instead. For more
information about indexes, see Section 8.3.1, “How MySQL Uses Indexes”.
When the innodb_stats_persistent
setting is enabled, run the ANALYZE
TABLE
statement for an
InnoDB
table after creating an index
on that table.
An index specification of the form
(
creates an
index with multiple key parts. Index key values are formed by
concatenating the values of the given key parts. For example
key_part1
,
key_part2
, ...)(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
. These keywords
are permitted 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.
The following sections describe different aspects of the
CREATE INDEX
statement:
For string columns, indexes can be created that use only the
leading part of column values, using
syntax to specify an index prefix length:
col_name
(length
)
Prefixes can be specified for
CHAR
,
VARCHAR
,
BINARY
, and
VARBINARY
key parts.
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.
Prefix support and lengths of prefixes (where supported) are
storage engine dependent. For example, a prefix can be up to
767 bytes long for InnoDB
tables or 3072 bytes if the
innodb_large_prefix
option
is enabled. For MyISAM
tables,
the prefix length limit is 1000 bytes. The
NDB
storage engine does not
support prefixes (see
Section 18.1.7.6, “Unsupported or Missing Features in NDB Cluster”).
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.
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.
FULLTEXT
indexes are supported only for
InnoDB
and
MyISAM
tables and can include only
CHAR
,
VARCHAR
, and
TEXT
columns. Indexing always
happens over the entire column; column prefix indexing is not
supported and any prefix length is ignored if specified. See
Section 12.10, “Full-Text Search Functions”, for details of operation.
The MyISAM
,
InnoDB
,
NDB
, and
ARCHIVE
storage engines support
spatial columns such as POINT
and
GEOMETRY
.
(Section 11.4, “Spatial Data Types”, describes the spatial data
types.) However, support for spatial column indexing varies
among engines. Spatial and nonspatial indexes on spatial columns
are available according to the following rules.
Spatial indexes on spatial columns (created using
SPATIAL INDEX
) have these characteristics:
Available only for MyISAM
tables. Specifying SPATIAL INDEX
for
other storage engines results in an error.
Indexed columns must be NOT NULL
.
Column prefix lengths are prohibited. The full width of each column is indexed.
Nonspatial indexes on spatial columns (created with
INDEX
, UNIQUE
, or
PRIMARY KEY
) have these characteristics:
Permitted for any storage engine that supports spatial
columns except ARCHIVE
.
Columns can be NULL
unless the index is a
primary key.
For each spatial column in a non-SPATIAL
index except POINT
columns, a
column prefix length must be specified. (This is the same
requirement as for indexed
BLOB
columns.) The prefix
length is given in bytes.
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.
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.
KEY_BLOCK_SIZE
is not supported at the
index level for InnoDB
tables.
See Section 13.1.17, “CREATE TABLE Statement”.
index_type
Some storage engines permit you to specify an index type when creating an index. For example:
CREATE TABLE lookup (id INT) ENGINE = MEMORY; CREATE INDEX id_index ON lookup (id) USING BTREE;
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.
The index_type
clause cannot be
used for FULLTEXT INDEX
or
SPATIAL INDEX
specifications. Full-text
index implementation is storage engine dependent. Spatial
indexes are implemented as R-tree indexes.
BTREE
indexes are implemented by the
NDB
storage engine as T-tree
indexes.
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.
Use of the index_type
option
before the ON
clause is
deprecated; expect support for use of the option in this
position to be removed in a future MySQL release. If an
tbl_name
index_type
option is given in
both the earlier and later positions, the final option
applies.
TYPE
is recognized as a synonym for type_name
USING
. However,
type_name
USING
is the preferred form.
The following tables show index characteristics for the
storage engines that support the
index_type
option.
Table 13.2 InnoDB Storage Engine Index Characteristics
Index Class | Index Type | Stores NULL VALUES | Permits Multiple NULL Values | IS NULL Scan Type | IS NOT NULL Scan Type |
---|---|---|---|---|---|
Primary key | BTREE |
No | No | N/A | N/A |
Unique | BTREE |
Yes | Yes | Index | Index |
Key | BTREE |
Yes | Yes | Index | Index |
FULLTEXT |
N/A | Yes | Yes | Table | Table |
Table 13.3 MyISAM Storage Engine Index Characteristics
Index Class | Index Type | Stores NULL VALUES | Permits Multiple NULL Values | IS NULL Scan Type | IS NOT NULL Scan Type |
---|---|---|---|---|---|
Primary key | BTREE |
No | No | N/A | N/A |
Unique | BTREE |
Yes | Yes | Index | Index |
Key | BTREE |
Yes | Yes | Index | Index |
FULLTEXT |
N/A | Yes | Yes | Table | Table |
SPATIAL |
N/A | No | No | N/A | N/A |
Table 13.4 MEMORY Storage Engine Index Characteristics
Index Class | Index Type | Stores NULL VALUES | Permits Multiple NULL Values | IS NULL Scan Type | IS NOT NULL Scan Type |
---|---|---|---|---|---|
Primary key | BTREE |
No | No | N/A | N/A |
Unique | BTREE |
Yes | Yes | Index | Index |
Key | BTREE |
Yes | Yes | Index | Index |
Primary key | HASH |
No | No | N/A | N/A |
Unique | HASH |
Yes | Yes | Index | Index |
Key | HASH |
Yes | Yes | Index | Index |
Table 13.5 NDB Storage Engine Index Characteristics
Index Class | Index Type | Stores NULL VALUES | Permits Multiple NULL Values | IS NULL Scan Type | IS NOT NULL Scan Type |
---|---|---|---|---|---|
Primary key | BTREE |
No | No | Index | Index |
Unique | BTREE |
Yes | Yes | Index | Index |
Key | BTREE |
Yes | Yes | Index | Index |
Primary key | HASH |
No | No | Table (see note 1) | Table (see note 1) |
Unique | HASH |
Yes | Yes | Table (see note 1) | Table (see note 1) |
Key | HASH |
Yes | Yes | Table (see note 1) | Table (see note 1) |
Table note:
1. If USING HASH
is specified that
prevents creation of an implicit ordered index.
WITH PARSER
parser_name
This option can be used only with
FULLTEXT
indexes. It associates a parser
plugin with the index if full-text indexing and searching
operations need special handling. See
The MySQL Plugin API, for details on creating
plugins.
COMMENT
'
string
'
Index definitions can include an optional comment of up to 1024 characters.
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.7, “ALTER TABLE Statement”
Indexes on variable-width columns of
NDBCLUSTER
tables are created
online; that is, without any table copying. The table is not
locked against access from other NDB Cluster API nodes, although
it is locked against other operations on the
same API node for the duration of the
operation. This is done automatically by the server whenever it
determines that it is possible to do so; you do not have to use
any special SQL syntax or server options to cause it to happen.
In standard MySQL 5.6 releases, it is not possible
to override the server when it determines that an index is to be
created without table copying. In NDB Cluster, you can create
indexes offline (which causes the table to be locked to all API
nodes in the cluster) using the OFFLINE
keyword. The rules and limitations governing CREATE
OFFLINE INDEX
and CREATE ONLINE
INDEX
are the same as for ALTER OFFLINE TABLE
... ADD INDEX
and ALTER ONLINE TABLE ... ADD
INDEX
. You cannot cause the noncopying creation of an
index that would normally be created offline by using the
ONLINE
keyword: If it is not possible to
perform the CREATE INDEX
operation without table copying, the server ignores the
ONLINE
keyword. For more information, see
Section 18.5.11, “Online Operations with ALTER TABLE in NDB Cluster”.
The ONLINE
and OFFLINE
keywords are available only in NDB Cluster; attempting to use
these keywords in standard MySQL Server 5.6
releases results in a syntax error. The
ONLINE
and OFFLINE
keywords are deprecated in MySQL NDB Cluster 7.3; they continue
to be supported in MySQL NDB Cluster 7.4, but they are subject
to removal in a future NDB Cluster release.