•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 that use the REDUNDANT  or COMPACT  row format. The prefix length limit is 3072 bytes for InnoDB  tables that use the DYNAMIC  or COMPRESSED row format. For MyISAM  tables, the prefix length limit is 1000 bytes. The NDB  storage engine does not support prefixes (see Section 23.1.7.6, “Unsupported or Missing Features in NDB Cluster” ). 

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

## 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 full col1  value and a prefix of the col2  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. 

