CREATE INDEX Statement 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’; CREATE TABLE employees ( data JSON, INDEX ((data->>’$.name’)) ); 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: The syntax fails because: To index the  JSON  column, you could try using the  CAST()  function as follows: •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. 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: 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’; • 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). 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 ( 2517