## Unique Indexes 

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) 

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.10, “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. 

## Creating multi-valued Indexes 

{ "user":"Bob", "user_id":31, "zipcode":[94477,94536] } 

You can create a multi-valued index in a CREATE TABLE , ALTER TABLE , or CREATE 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 

