13.1.13 CREATE INDEX Syntax

CREATE [ONLINE | OFFLINE] [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_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 PARSER parser_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 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.

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.7, “ALTER TABLE Syntax”. 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 (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. 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:

• Column Prefix Key Parts

• Unique Indexes

• Full-Text Indexes

• Spatial Indexes

• Index Options

• Table Copying and Locking Options

Column Prefix Key Parts

For string columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix 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.6.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.

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.

Full-Text Indexes

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.9, “Full-Text Search Functions”, for details of operation.

Spatial Indexes

The MyISAM, InnoDB, NDB, and ARCHIVE storage engines support spatial columns such as POINT and GEOMETRY. (Section 11.5, “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.

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.

  KEY_BLOCK_SIZE is not supported at the index level for InnoDB tables. See Section 13.1.17, “CREATE TABLE Syntax”.

• 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.

  Table 13.1 Index Types Per Storage Engine

  Storage Engine	Permissible Index Types
  InnoDB	BTREE
  MyISAM	BTREE
  MEMORY/HEAP	HASH, BTREE
  NDB	HASH, BTREE (see note in text)

  
  

  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.

  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.

  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 Section 24.2, “The MySQL Plugin API”, for details on creating plugins.

• COMMENT 'string'

  Index definitions can include an optional comment of up to 1024 characters.

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.7, “ALTER TABLE Syntax”

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 13.1.7.2, “ALTER TABLE Online Operations 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.