Although nonfunctional key parts support a prefix length specification, this is not possible for functional key parts. The solution is to use SUBSTRING()  (or CAST() , as described later in this section). For a functional key part containing the SUBSTRING()  function to be used in a query, the WHERE  clause must contain SUBSTRING()  with the same arguments. In the following example, only the second SELECT  is able to use the index because that is the only query in which the arguments to SUBSTRING()  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: 

•The ->>  operator translates into JSON_UNQUOTE(JSON_EXTRACT(...)) . 

• JSON_UNQUOTE()  returns a value with a data type of LONGTEXT , and the hidden generated column thus is assigned the same data type. 

•MySQL cannot index LONGTEXT  columns specified without a prefix length on the key part, and prefix lengths are not permitted in functional key parts. 

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 the VARCHAR(30)  data type, which can be indexed. But this approach produces a new issue when trying to use the index: 

• CAST()  returns a string with the collation utf8mb4_0900_ai_ci  (the server default collation). 

• JSON_UNQUOTE()  returns a string with the collation utf8mb4_bin  (hard coded). 

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

•Solution 1. Assign the indexed expression the same collation as JSON_UNQUOTE() : 

CREATE TABLE employees ( 

