Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  / ...  / SQL Statements  / Data Definition Statements  /  CREATE INDEX Statement

13.1.14 CREATE INDEX Statement

CREATE [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 withCREATE TABLE. SeeSection 13.1.18, “CREATE TABLE Statement”. This guideline is especially important forInnoDB 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 anALTER TABLE statement to create indexes. SeeSection 13.1.8, “ALTER TABLE Statement”.CREATE INDEX cannot be used to create aPRIMARY KEY; useALTER TABLE instead. For more information about indexes, seeSection 8.3.1, “How MySQL Uses Indexes”.

InnoDB supports secondary indexes on virtual columns. For more information, seeSection 13.1.18.8, “Secondary Indexes and Generated Columns”.

When theinnodb_stats_persistent setting is enabled, run theANALYZE TABLE statement for anInnoDB 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 fromcol1,col2, andcol3.

Akey_part specification can end withASC orDESC. 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 theCREATE INDEX statement:

Column Prefix Key Parts

For string columns, indexes can be created that use only the leading part of column values, usingcol_name(length) syntax to specify an index prefix length:

As of MySQL 5.7.17, if a specified index prefix exceeds the maximum column data type size,CREATE INDEX handles the index as follows:

  • 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 thename column (assuming thatname 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 entirename 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 upINSERT operations.

Unique Indexes

AUNIQUE 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 aUNIQUE index, the column values must be unique within the prefix length. AUNIQUE index permits multipleNULL values for columns that can containNULL.

If a table has aPRIMARY KEY orUNIQUE NOT NULL index that consists of a single column that has an integer type, you can use_rowid to refer to the indexed column inSELECT statements, as follows:

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

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

Full-Text Indexes

FULLTEXT indexes are supported only forInnoDB andMyISAM tables and can include onlyCHAR,VARCHAR, andTEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. SeeSection 12.9, “Full-Text Search Functions”, for details of operation.

Spatial Indexes

TheMyISAM,InnoDB,NDB, andARCHIVE storage engines support spatial columns such asPOINT andGEOMETRY. (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 usingSPATIAL INDEX) have these characteristics:

  • Available only forMyISAM andInnoDB tables. SpecifyingSPATIAL INDEX for other storage engines results in an error.

  • Indexed columns must beNOT NULL.

  • Column prefix lengths are prohibited. The full width of each column is indexed.

Nonspatial indexes on spatial columns (created withINDEX,UNIQUE, orPRIMARY KEY) have these characteristics:

  • Permitted for any storage engine that supports spatial columns exceptARCHIVE.

  • Columns can beNULL unless the index is a primary key.

  • For each spatial column in a non-SPATIAL index exceptPOINT columns, a column prefix length must be specified. (This is the same requirement as for indexedBLOB 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 haveNULL values only forInnoDB,MyISAM, andMEMORY tables.

Index Options

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

  • KEY_BLOCK_SIZE [=]value

    ForMyISAM 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. AKEY_BLOCK_SIZE value specified for an individual index definition overrides a table-levelKEY_BLOCK_SIZE value.

    KEY_BLOCK_SIZE is not supported at the index level forInnoDB tables. SeeSection 13.1.18, “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 anindex_type clause in index definitions.

    Table 13.1 Index Types Per Storage Engine

    Storage EnginePermissible Index Types
    InnoDBBTREE
    MyISAMBTREE
    MEMORY/HEAPHASH,BTREE
    NDBHASH,BTREE (see note in text)

    Theindex_type clause cannot be used forFULLTEXT INDEX orSPATIAL INDEX specifications. Full-text index implementation is storage engine dependent. Spatial indexes are implemented as R-tree indexes.

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

    Note

    For indexes onNDB table columns, theUSING 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 anNDB 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 moreNULL columns of anNDB table, the hash index can be used only to look up literal values, which means thatIS [NOT] NULL conditions require a full scan of the table. One workaround is to make sure that a unique index using one or moreNULL columns on such a table is always created in such a way that it includes the ordered index; that is, avoid employingUSING 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 recognizesRTREE as a type name, but currently this cannot be specified for any storage engine.

    Note

    Use of theindex_type option before theONtbl_name clause is deprecated; you should expect support for use of the option in this position to be removed in a future MySQL release. If anindex_type option is given in both the earlier and later positions, the final option applies.

    TYPEtype_name is recognized as a synonym forUSINGtype_name. However,USING is the preferred form.

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

    Table 13.2 InnoDB Storage Engine Index Characteristics

    Index ClassIndex TypeStores NULL VALUESPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
    Primary keyBTREENoNoN/AN/A
    UniqueBTREEYesYesIndexIndex
    KeyBTREEYesYesIndexIndex
    FULLTEXTN/AYesYesTableTable
    SPATIALN/ANoNoN/AN/A

    Table 13.3 MyISAM Storage Engine Index Characteristics

    Index ClassIndex TypeStores NULL VALUESPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
    Primary keyBTREENoNoN/AN/A
    UniqueBTREEYesYesIndexIndex
    KeyBTREEYesYesIndexIndex
    FULLTEXTN/AYesYesTableTable
    SPATIALN/ANoNoN/AN/A

    Table 13.4 MEMORY Storage Engine Index Characteristics

    Index ClassIndex TypeStores NULL VALUESPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
    Primary keyBTREENoNoN/AN/A
    UniqueBTREEYesYesIndexIndex
    KeyBTREEYesYesIndexIndex
    Primary keyHASHNoNoN/AN/A
    UniqueHASHYesYesIndexIndex
    KeyHASHYesYesIndexIndex

    Table 13.5 NDB Storage Engine Index Characteristics

    Index ClassIndex TypeStores NULL VALUESPermits Multiple NULL ValuesIS NULL Scan TypeIS NOT NULL Scan Type
    Primary keyBTREENoNoIndexIndex
    UniqueBTREEYesYesIndexIndex
    KeyBTREEYesYesIndexIndex
    Primary keyHASHNoNoTable (see note 1)Table (see note 1)
    UniqueHASHYesYesTable (see note 1)Table (see note 1)
    KeyHASHYesYesTable (see note 1)Table (see note 1)

    Table note:

    1. IfUSING HASH is specified that prevents creation of an implicit ordered index.

  • WITH PARSERparser_name

    This option can be used only withFULLTEXT indexes. It associates a parser plugin with the index if full-text indexing and searching operations need special handling.InnoDB andMyISAM support full-text parser plugins. If you have aMyISAM table with an associated full-text parser plugin, you can convert the table toInnoDB usingALTER TABLE. SeeFull-Text Parser Plugins andWriting Full-Text Parser Plugins for more information.

  • COMMENT 'string'

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

    TheMERGE_THRESHOLD for index pages can be configured for individual indexes using theindex_optionCOMMENT clause of theCREATE INDEX statement. For example:

    CREATE TABLE t1 (id INT);CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

    If the page-full percentage for an index page falls below theMERGE_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 defaultMERGE_THRESHOLD value is 50, which is the previously hardcoded value.

    MERGE_THRESHOLD can also be defined at the index level and table level usingCREATE TABLE andALTER TABLE statements. For more information, seeSection 14.8.12, “Configuring the Merge Threshold for Index Pages”.

Table Copying and Locking Options

ALGORITHM andLOCK 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 theALTER TABLE statement. For more information, seeSection 13.1.8, “ALTER TABLE Statement”

NDB Cluster formerly supported onlineCREATE INDEX operations using an alternative syntax that is no longer supported. NDB Cluster now supports online operations using the sameALGORITHM=INPLACE syntax used with the standard MySQL Server. SeeSection 21.6.12, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.