Learn how Multi-Valued Indexing in MySQL 8.0 can enhance query performance by efficiently indexing and querying JSON arrays. Discover the benefits, implementation steps, and considerations for optimizing your MySQL database.

1. Multi-Valued Indexing in MySQL 8.0
2. Understanding Multi-Valued Indexes and their benefits
3. Creating Multi-Valued Indexes in MySQL 8.0
   1. Syntax and Examples of Creating Multi-Valued Indexes
   2. Composite Indexing in Multi-Valued Indexes
   3. Unique Indexing in Multi-Valued Indexes
4. Accessing Multi-Valued Indexes using Functions
   1. Exploring MEMBER_OF(), JSON_CONTAINS(), and JSON_OVERLAPS() Functions
   2. Query Optimization Using Multi-Valued Indexes
5. Key Considerations for Multi-Valued Indexing

Multi-Valued Indexing in MySQL 8.0

MySQL 8.0 introduces a game-changing feature called Multi-Valued Indexing, which revolutionizes the way JSON arrays are indexed and accessed. In the past, working with JSON arrays in MySQL posed challenges, as searching and querying within these arrays required full table scans, resulting in slow and inefficient operations. However, Multi-Valued Indexing changes the game by providing a solution that enables efficient indexing and querying of data within JSON arrays.

A Multi-Valued Index is a secondary index defined on a column that stores an array of values. Unlike traditional indexes that have a one-to-one relationship between index and data records, Multi-Valued Indexes allow for a many-to-one relationship, where each element within the array generates an index record. This means that querying and accessing data within JSON arrays becomes simple, fast, and highly optimized.

Understanding Multi-Valued Indexes and their benefits

A Multi-Valued Index in MySQL 8.0 is a powerful feature that allows for efficient indexing and retrieval of data stored within JSON arrays. Unlike a normal index, which has a one-to-one relationship between index records and data records (1:1), a Multi-Valued Index enables a many-to-one relationship (N:1), where multiple index records can point to a single data record.

To illustrate this concept, let’s consider an example where we have a multi-valued index assigned to the “postal_codes” field. Suppose we have a JSON document representing location data:

 
{
    "city": "New York", 
    "state": "NY", 
    "country": "America",
    "postal_codes": [10001,10001,10002,10002,10003,10004]
}
	

In this case, a multi-valued index will be generated for each element in the “postal_codes” array. This means that for the given JSON document, six index records will be created, each pointing to the same data record. The Multi-Valued Index effectively allows for direct access to each element within the array, improving query performance when searching for specific postal codes. This feature greatly enhances the efficiency and speed of operations involving JSON data, making it an invaluable tool for applications that heavily rely on JSON arrays.

Creating Multi-Valued Indexes in MySQL 8.0

Syntax and Examples of Creating Multi-Valued Indexes

To create a multi-valued index in MySQL, you can use either the ALTER TABLE or CREATE INDEX statement with the CAST(… AS … ARRAY) expression. Here’s an example using the location_data table:

Table Structure:

 
CREATE TABLE `location_data` (
  `location_id` int NOT NULL AUTO_INCREMENT,
  `location_info` json DEFAULT NULL,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`location_id`)
)
	

Using ALTER TABLE:

 
ALTER TABLE location_data ADD INDEX idx_postal_codes((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));
	

Using CREATE INDEX:

 
CREATE INDEX idx_postal_codes ON location_data ((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));
	

After applying the multi-valued index on the location_data table, the updated table structure will include the index definition.

 
mysql> show create table location_data\G
*************************** 1. row ***************************
       Table: location_data
Create Table: CREATE TABLE `location_data` (
  `location_id` int NOT NULL AUTO_INCREMENT,
  `location_info` json DEFAULT NULL,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`location_id`),
  KEY `idx_postal_codes` ((cast(json_extract(`location_info`,_utf8mb4'$.postal_codes') as unsigned array)))
) ENGINE=InnoDB AUTO_INCREMENT=101865 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
	

Composite Indexing in Multi-Valued Indexes

A multi-valued index can be defined as a composite index, allowing you to combine multiple columns into a single index. This improves data organization and enhances query performance by efficiently indexing and retrieving multiple values.

Example:

To establish a composite index on the location_info and modified columns of the location_data table, use the following query:

 
ALTER TABLE location_data ADD INDEX idx_postal_codes_modified((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)), modified);
	

This composite multi-valued index enables efficient searching based on both the postal_codes array and the modified column.

Unique Indexing in Multi-Valued Indexes

A multi-valued index can also be defined as a unique index, ensuring the uniqueness of values in the indexed column(s) and preventing duplicate entries.

Example:

To create a unique multi-valued index on the postal_codes array within the location_info column, use the following query:

 
ALTER TABLE location_data ADD UNIQUE INDEX idx_postal_codes((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));
	

When attempting to insert duplicate values across different records, a duplicate error will occur, causing the insertion to fail. However, if duplicate values are inserted within the same record, the insertion will be successful.

For example, the first insertion below succeeds because the duplicates are within the same record, while the second insertion fails due to duplicates across different records:

 
— Insert Success

mysql> INSERT INTO location_data (location_info) VALUES ('{"city":"New York", "state":"NY", "country": "America","postal_codes":[10001, 10001, 10002, 10002, 10003, 10004]}');
Query OK, 1 row affected (0.01 sec)

— Insert Failed

mysql> INSERT INTO location_data (location_info) VALUES ('{"city":"New York", "state":"NY", "country": "America","postal_codes":[10001, 10002, 10003, 10004]}');
ERROR 1062 (23000): Duplicate entry '[10001, ' for key 'location_data.idx_postal_codes'
	

By utilizing multi-valued indexes, including composite and unique indexing, you can optimize your database’s performance and enforce data integrity.

Accessing Multi-Valued Indexes using Functions

Exploring MEMBER_OF(), JSON_CONTAINS(), and JSON_OVERLAPS() Functions

The Multi-Valued Index in MySQL enables efficient retrieval of records by utilizing three functions: MEMBER_OF(), JSON_CONTAINS(), and JSON_OVERLAPS(). These functions are specified in the WHERE clause of the query to filter and search for specific values within multi-valued indexed columns.

In this example, let’s focus on using the MEMBER_OF() function to search for the postal code 85001 within the location_info column of the location_data table:

 
mysql> SELECT * FROM location_data WHERE 85001 MEMBER OF(location_info->"$.postal_codes");
+-------------+--------------------------------------------------------------------------------------------------------+---------------------+
| location_id | location_info                                                                                          | modified            |
+-------------+--------------------------------------------------------------------------------------------------------+---------------------+
|          11 | {"city": "Phoenix", "state": "AZ", "country": "America", "postal_codes": [85001, 85002, 85003, 85004]} | 2023-05-28 19:50:16 |
+-------------+--------------------------------------------------------------------------------------------------------+---------------------+
1 row in set (0.00 sec)
	

The query returns the records that contain the specified postal code within their postal_codes array. In this case, it retrieves the record with location_id 11, which has the postal code 85001.

By using the MEMBER_OF() function, you can efficiently search for specific values within multi-valued indexed columns and retrieve the relevant records.

Explain Plan :

 
mysql> EXPLAIN SELECT * FROM location_data WHERE 85001 MEMBER OF(location_info->"$.postal_codes");
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | location_data | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100515 |   100.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
	

The current query performs a full table scan, taking approximately 150 ms to execute.

Query Optimization Using Multi-Valued Indexes

When dealing with large datasets, query performance becomes crucial. By creating a multi-valued index on the postal_codes array within the location_info column, you can significantly improve the query performance for searching and retrieving records based on postal codes.

To optimize the query, you can add the multi-valued index using the following ALTER TABLE statement:

 
mysql> ALTER TABLE location_data ADD INDEX idx_postal_codes((CAST(location_info -> '$.postal_codes' AS UNSIGNED ARRAY)));
Query OK, 0 rows affected (0.96 sec)
Records: 0  Duplicates: 0  Warnings: 0
	

After adding the multi-valued index, the query’s execution plan changes. Instead of performing a full table scan, it utilizes the index to efficiently retrieve the desired records. The query processed only one row, resulting in an execution time of 1 ms.

The EXPLAIN statement demonstrates the improved query plan:

 
mysql> EXPLAIN SELECT * FROM location_data WHERE 85001 MEMBER OF(location_info->"$.postal_codes");
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | location_data | NULL       | ref  | idx_postal_codes | idx_postal_codes | 9       | const |    1 |   100.00 | Using where |
+----+-------------+---------------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
	

The use of appropriate functions and proper index creation can greatly enhance the efficiency of your database queries.

With the multi-valued index in place, the query’s performance is significantly improved, reducing the number of rows examined and reducing the execution time from 150 ms to just 1 ms. This optimization enhances the overall efficiency of your queries and improves the responsiveness of your database system.

Key Considerations for Multi-Valued Indexing

• DML operations for multi-valued indexes are handled similarly to normal indexes, with the distinction that there may be multiple inserts or updates for a single clustered index record.

• When indexing data, the multi-valued index does not consider empty arrays, so searching for empty values using the index will not yield any matches.

• It’s important to note that JSON null values are not allowed within multi-valued indexed arrays. If any element within the array is null, it will be treated as a JSON null value and can result in an error due to an invalid JSON value.

• A composite index can only accommodate one multi-valued index, so it’s essential to consider the index structure and column combinations accordingly.

• Furthermore, multi-valued indexes do not support the ordering of values, making them unsuitable for use as primary keys. Additionally, they cannot be utilized with ASC or DESC orders.

Multi-valued indexes provide an effective solution for indexing and querying JSON arrays in MySQL. The use of functions like MEMBER_OF(), JSON_CONTAIN(), and JSON_OVERLAPS() allows for efficient retrieval of records based on specific array values. These indexes improve query performance by enabling direct access to array elements, eliminating the need for time-consuming table scans.

However, it is important to note that multi-valued indexes have certain limitations, including the inability to support ordering and primary keys. By considering these limitations and making informed decisions, multi-valued indexes can greatly enhance the efficiency and effectiveness of MySQL queries involving JSON arrays.

For more comprehensive information on multi-valued indexes, you can refer to the official MySQL documentation on multi-valued indexes. It provides in-depth insights and guidance on utilizing this feature to its fullest potential.

To delve deeper into insightful discussions and broaden your knowledge, stay updated for our upcoming posts. Visit our website to explore a diverse range of informative blogs that cover a wide array of topics in database consulting, support, and technology. If you found this blog post beneficial, you will likely discover other articles that pique your interest and offer valuable insights for your business or career.

If you require expert assistance in managing your MySQL databases, feel free to reach out to us. Our highly skilled professionals are committed to delivering customized solutions that cater to your specific needs, ensuring the security, optimization, and accessibility of your data. Let us assist you in unlocking the full potential of your data.