Summary: in this tutorial, you will learn how to create an index for JSON data in MySQL using functional indexes.
Setting up a sample table
We’ll use the products table from the sample database as a data source for creating JSON data:
First, create a new table called product_info:
CREATE TABLE product_results( id INT AUTO_INCREMENT PRIMARY KEY, data JSON );Code language: SQL (Structured Query Language) (sql)The product_results table has two columns:
id: This is an auto-increment primary key column.data: This column has the data type of JSON that stores the JSON data.
Next, create JSON data from the columns of the products table and insert it into the product_results table:
INSERT INTO product_results(data) SELECT JSON_OBJECT( 'buyPrice', buyPrice, 'MSRP', msrp, 'quantityInStock', quantityInStock, 'productName', productName ) FROM products;Code language: SQL (Structured Query Language) (sql)The statement inserts 110 rows into the product_results table.
Then, query data from the product_results table:
SELECT * FROM product_results;Code language: SQL (Structured Query Language) (sql)Output:
+-----+--------------------------------------------------------------------------------------------------------------------------+ | id | data | +-----+--------------------------------------------------------------------------------------------------------------------------+ | 1 | {"MSRP": 95.70, "buyPrice": 48.81, "productName": "1969 Harley Davidson Ultimate Chopper", "quantityInStock": 7933} | | 2 | {"MSRP": 214.30, "buyPrice": 98.58, "productName": "1952 Alpine Renault 1300", "quantityInStock": 7305} | | 3 | {"MSRP": 118.94, "buyPrice": 68.99, "productName": "1996 Moto Guzzi 1100i", "quantityInStock": 6625} | ...Code language: SQL (Structured Query Language) (sql)After that, find the product with the name 'P-51-D Mustang':
SELECT data ->> '$.productName', data ->> '$.buyPrice' FROM product_results WHERE data ->> '$.productName' = 'P-51-D Mustang';Code language: SQL (Structured Query Language) (sql)It returns the following product:
+--------------------------+-----------------------+ | data ->> '$.productName' | data ->> '$.buyPrice' | +--------------------------+-----------------------+ | P-51-D Mustang | 49.00 | +--------------------------+-----------------------+ 1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)The query works as expected but it has to scan all the rows in the product_results table to find the product.
Finally, show how the query optimizer executes the query by using the EXPLAIN statement:
EXPLAIN SELECT data ->> '$.productName', data ->> '$.buyPrice' FROM product_results WHERE data ->> '$.productName' = 'P-51-D Mustang' \G;Code language: SQL (Structured Query Language) (sql)Output:
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: product_results partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 110 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)Code language: SQL (Structured Query Language) (sql)To speed up the query, you need to index the JSON in the data column. However, MySQL does not support indexing JSON data directly.
Fortunately, you can use functional indexes, which have been available since MySQL 8.0.13, to index JSON data.
Indexing JSON data using functional indexes
A functional index is an index based on an expression rather than a column. The following statement adds an index to the product_results table:
ALTER TABLE product_results ADD INDEX idx_product_name( (data ->> '$.productName') );Code language: SQL (Structured Query Language) (sql)But it returns an error:
ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.Code language: SQL (Structured Query Language) (sql)The reason is that the ->> operator returns a value of the TEXT type that is not indexable.
The error message also shows that we need to use the CAST operator to cast the value to an indexable type e.g., VARCHAR in this case:
ALTER TABLE product_results ADD INDEX idx_product_name ( ( CAST( data ->> '$.productName' AS CHAR(255) ) COLLATE utf8mb4_bin ) );Code language: SQL (Structured Query Language) (sql)The index is created successfully.
Note that casting using CHAR instructs MySQL to infer a VARCHAR type.
Now, you can query the products by the buy price from the JSON data:
SELECT data ->> '$.productName', data ->> '$.buyPrice' FROM product_results WHERE data ->> '$.productName' = 'P-51-D Mustang';Code language: SQL (Structured Query Language) (sql)It returns the same result set as before. But this time, it uses the functional index idx_product_name:
EXPLAIN SELECT data ->> '$.productName', data ->> '$.buyPrice' FROM product_results WHERE data ->> '$.productName' = 'P-51-D Mustang' \G;Code language: SQL (Structured Query Language) (sql)Output:
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: product_results partitions: NULL type: ref possible_keys: idx_product_name key: idx_product_name key_len: 1023 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)Code language: SQL (Structured Query Language) (sql)Summary
- Use functional indexes to index JSON data to improve the query speed.
