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.20, “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.9, “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” . 

InnoDB  supports secondary indexes on virtual columns. For more information, see Section 13.1.20.9, “Secondary Indexes and Generated Columns” . 

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

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

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. 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 the CREATE INDEX  statement: 

## Column Prefix Key Parts 

•Column Prefix Key Parts 

•Functional Key Parts 

•Unique Indexes 

•Full-Text Indexes 

•Multi-Valued Indexes 

•Spatial Indexes 

•Index Options 

•Table Copying and Locking Options 

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. 

