MySQL :: MySQL 8.0 Reference Manual :: 15.1.15 CREATE INDEX Statement (original) (raw)

15.1.15 CREATE INDEX Statement

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] '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 15.1.20, “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. See Section 15.1.9, “ALTER TABLE Statement”.CREATE INDEX cannot be used to create a PRIMARY KEY; useALTER TABLE instead. For more information about indexes, see Section 10.3.1, “How MySQL Uses Indexes”.

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

When the innodb_stats_persistent setting is enabled, run the ANALYZE TABLE statement for anInnoDB table after creating an index on that table.

Beginning with MySQL 8.0.17, the expr for a keypart specification can take the form (CAST _`jsonexpression`_ AS _`type`_ ARRAY) to create a multi-valued index on a JSON column. See Multi-Valued Indexes.

An index specification of the form(_`keypart1`_,_`keypart2`_, ...) 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.

A keypart specification can end withASC 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.ASC and DESC are also not supported for multi-valued indexes. As of MySQL 8.0.12,ASC and DESC are not permitted for SPATIAL indexes.

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, using_`colname`_(_`length`_) syntax to specify an index prefix length:

If a specified index prefix exceeds the maximum column data type size, CREATE INDEX handles the index as follows:

The statement shown here creates an index using the first 10 characters of the name 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.

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 fullcol1 value and a prefix of thecol2 value consisting of its first 10 characters:

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table. Examples:

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

An index with multiple key parts can mix nonfunctional and functional key parts.

ASC and DESC are supported for functional key parts.

Functional key parts must adhere to the following rules. An error occurs if a key part definition contains disallowed constructs.

INDEX ((col1 + col2), (col3 - col4))

This produces an error; the expressions are not enclosed within parentheses: 

INDEX (col1 + col2, col3 - col4)  
INDEX ((col1), (col2))

Instead, write the key parts as nonfunctional key parts, without parentheses: 

INDEX (col1, col2)

For CREATE TABLE ... LIKE, the destination table preserves functional key parts from the original table.

Functional indexes are implemented as hidden virtual generated columns, which has these implications:

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 useSUBSTRING() (orCAST(), as described later in this section). For a functional key part containing theSUBSTRING() function to be used in a query, the WHERE clause must containSUBSTRING() with the same arguments. In the following example, only the secondSELECT is able to use the index because that is the only query in which the arguments toSUBSTRING() match the index specification:

CREATE TABLE tbl (
  col1 LONGTEXT,
  INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';

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:

CREATE TABLE employees (
  data JSON,
  INDEX ((data->>'$.name'))
);

The syntax fails because:

To index the JSON column, you could try using the CAST() function as follows:

CREATE TABLE employees (
  data JSON,
  INDEX ((CAST(data->>'$.name' AS CHAR(30))))
);

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

As a result, there is a collation mismatch between the indexed expression in the preceding table definition and theWHERE 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 stripsCAST() 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):

CREATE TABLE employees (  
  data JSON,  
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))  
);  
INSERT INTO employees VALUES  
  ('{ "name": "james", "salary": 9000 }'),  
  ('{ "name": "James", "salary": 10000 }'),  
  ('{ "name": "Mary", "salary": 12000 }'),  
  ('{ "name": "Peter", "salary": 8000 }');  
SELECT * FROM employees WHERE data->>'$.name' = 'James';

The ->> operator is the same asJSON_UNQUOTE(JSON_EXTRACT(...)), andJSON_UNQUOTE() returns a string with collation utf8mb4_bin. The comparison is thus case-sensitive, and only one row matches: 

+------------------------------------+  
| data                               |  
+------------------------------------+  
| {"name": "James", "salary": 10000} |  
+------------------------------------+  
CREATE TABLE employees (  
  data JSON,  
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))  
);  
INSERT INTO employees VALUES  
  ('{ "name": "james", "salary": 9000 }'),  
  ('{ "name": "James", "salary": 10000 }'),  
  ('{ "name": "Mary", "salary": 12000 }'),  
  ('{ "name": "Peter", "salary": 8000 }');  
SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';

CAST() returns a string with collationutf8mb4_0900_ai_ci, so the comparison case-insensitive and two rows match: 

+------------------------------------+  
| data                               |  
+------------------------------------+  
| {"name": "james", "salary": 9000}  |  
| {"name": "James", "salary": 10000} |  
+------------------------------------+

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):

mysql> CREATE TABLE employees (
         data JSON,
         generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
       );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> INSERT INTO employees (data)
       VALUES ('{"name": "james"}'), ('{"name": "James"}');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "James"} | James         |
+-------------------+---------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "james"} | james         |
| {"name": "James"} | James         |
+-------------------+---------------+
2 rows in set (0.01 sec)

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 aUNIQUE 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 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:

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

Multi-Valued Indexes

As of MySQL 8.0.17, InnoDB supports multi-valued indexes. A multi-valued index is a secondary index defined on a column that stores an array of values. A“normal” index has one index record for each data record (1:1). A multi-valued index can have multiple index records for a single data record (N:1). Multi-valued indexes are intended for indexing JSON arrays. For example, a multi-valued index defined on the array of zip codes in the following JSON document creates an index record for each zip code, with each index record referencing the same data record.

{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}
Creating multi-valued Indexes

You can create a multi-valued index in aCREATE TABLE,ALTER TABLE, orCREATE INDEX statement. This requires using CAST(... AS ... ARRAY) in the index definition, which casts same-typed scalar values in a JSON array to an SQL data type array. A virtual column is then generated transparently with the values in the SQL data type array; finally, a functional index (also referred to as a virtual index) is created on the virtual column. It is the functional index defined on the virtual column of values from the SQL data type array that forms the multi-valued index.

The examples in the following list show the three different ways in which a multi-valued index zips can be created on an array $.zipcode on aJSON column custinfo in a table named customers. In each case, the JSON array is cast to an SQL data type array ofUNSIGNED integer values.

CREATE TABLE customers (  
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
    custinfo JSON,  
    INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )  
    );  
CREATE TABLE customers (  
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
    custinfo JSON  
    );  
ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );  
CREATE TABLE customers (  
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
    custinfo JSON  
    );  
CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

A multi-valued index can also be defined as part of a composite index. This example shows a composite index that includes two single-valued parts (for the id andmodified columns), and one multi-valued part (for the custinfo column):

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );

ALTER TABLE customers ADD INDEX comp(id, modified,
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

Only one multi-valued key part can be used in a composite index. The multi-valued key part may be used in any order relative to the other parts of the key. In other words, the ALTER TABLE statement just shown could have usedcomp(id, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)) (or any other ordering) and still have been valid.

Using multi-valued Indexes

The optimizer uses a multi-valued index to fetch records when the following functions are specified in aWHERE clause:

We can demonstrate this by creating and populating thecustomers table using the followingCREATE TABLE and INSERT statements:

mysql> CREATE TABLE customers (
    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     custinfo JSON
    ->     );
Query OK, 0 rows affected (0.51 sec)

mysql> INSERT INTO customers VALUES
    ->     (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    ->     (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    ->     (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    ->     (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    ->     (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

First we execute three queries on thecustomers table, one each usingMEMBER OF(),JSON_CONTAINS(), andJSON_OVERLAPS(), with the result from each query shown here:

mysql> SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2019-06-29 22:23:12 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2019-06-29 22:23:12 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2019-06-29 22:23:12 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2019-06-29 22:23:12 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

Next, we run EXPLAIN on each of the previous three queries:

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

None of the three queries just shown are able to use any keys. To solve this problem, we can add a multi-valued index on thezipcode array in the JSON column (custinfo), like this:

mysql> ALTER TABLE customers
    ->     ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

When we run the previous EXPLAIN statements again, we can now observe that the queries can (and do) use the index zips that was just created:

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers
    ->     WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | range | zips          | zips | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

A multi-valued index can be defined as a unique key. If defined as a unique key, attempting to insert a value already present in the multi-valued index returns a duplicate key error. If duplicate values are already present, attempting to add a unique multi-valued index fails, as shown here:

mysql> ALTER TABLE customers DROP INDEX zips;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE customers
    ->     ADD UNIQUE INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips'
mysql> ALTER TABLE customers
    ->     ADD INDEX zips((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0
Characteristics of Multi-Valued Indexes

Multi-valued indexes have the additional characteristics listed here:

Limitations and Restrictions on Multi-valued Indexes

Multi-valued indexes are subject to the limitations and restrictions listed here:

CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)

In this case, all values matching the JSON expression are stored in the index as a single flat array.

Spatial Indexes

The MyISAM,InnoDB,NDB, andARCHIVE storage engines support spatial columns such as POINT andGEOMETRY. (Section 13.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 have these characteristics:

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

Index Options

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

CREATE TABLE lookup (id INT) ENGINE = MEMORY;  
CREATE INDEX id_index ON lookup (id) USING BTREE;

Table 15.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 indextype clause in index definitions.
Table 15.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 indextype clause cannot be used for FULLTEXT INDEX or (prior to MySQL 8.0.12) SPATIAL INDEX specifications. Full-text index implementation is storage engine dependent. Spatial indexes are implemented as R-tree indexes.
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. 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 theNDB 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 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 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 recognizesRTREE as a type name, but currently this cannot be specified for any storage engine.
Note
Use of the indextype option before the ON_`tblname`_ clause is deprecated; expect support for use of the option in this position to be removed in a future MySQL release. If an_indextype_ option is given in both the earlier and later positions, the final option applies.
TYPE _`typename`_ is recognized as a synonym for USING_`typename`_. However,USING is the preferred form.
The following tables show index characteristics for the storage engines that support the_indextype_ option.
Table 15.2 InnoDB Storage Engine Index Characteristics
Index Class Index Type
----------- ----------
Primary key BTREE
Unique BTREE
Key BTREE
FULLTEXT N/A
SPATIAL N/A

Table 15.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 15.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 15.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. USING HASH prevents creation of an implicit ordered index. 
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 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 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 17.8.11, “Configuring the Merge Threshold for Index Pages”.

CREATE INDEX i1 ON t1 (c1) ENGINE_ATTRIBUTE='{"key":"value"}';

ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.
ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.