Indexing JSON Data in MySQL

Putting away JSON in a social data set is something designers have accomplished for a long while. There are different motivations behind why you would need to store JSON in a data set table – client inclinations and design information are two genuine models. The JSON Information Type was acquainted with MySQL in adaptation 5.7.8. This information type permits us to store substantial JSON in a data set section and run questions in view of the qualities in the JSON.

The Potential Issue

While putting away JSON information in MySQL, we can question the data set in view of values inside that JSON. We can achieve this utilizing an assortment of JSON explicit MySQL capabilities. The potential issue is that inquiry execution can debase over the long run as the quantity of columns increments. At the point when this occurs with different information types, one answer for assist with questioning execution is to add a list to at least one segments. Since MySQL 8.0.13, we have could make practical records. Practical files permit us to make lists in light of articulations, not segment information. We can use this component to make a list in light of JSON values.

Let’s get Started

Let’s create a straightforward table with a column containing JSON data before creating an index.

CREATE TABLE `vehicle` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `vehicle_data` JSON NOT NULL,

  PRIMARY KEY (`id`));

After creating the table, Add a few more facts.

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Austine","last_name":"Okill","gender":"Polygender","manufacturer":"GMC","model":"Sierra Hybrid","year":2006,"vin":"5TDBK3EH7BS492643","color":"Maroon"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Carrissa","last_name":"McGowing","gender":"Female","manufacturer":"Dodge","model":"Avenger","year":2000,"vin":"WBAPM7C53AE594359","color":"Maroon"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Mirabella","last_name":"O''Tuohy","gender":"Female","manufacturer":"Mercury","model":"Mountaineer","year":1997,"vin":"YV4902DZ7E2611356","color":"Red"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Marni","last_name":"Fratczak","gender":"Female","manufacturer":"Ford","model":"F150","year":2005,"vin":"WAUVT68EX5A254703","color":"Indigo"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Marcelo","last_name":"Cellone","gender":"Male","manufacturer":"Dodge","model":"Dakota","year":2004,"vin":"WBAPH5C55BF851378","color":"Turquoise"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Wilden","last_name":"Norwell","gender":"Bigender","manufacturer":"Mercury","model":"Sable","year":1996,"vin":"WAUHFAFL1EA004615","color":"Turquoise"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"York","last_name":"Hemerijk","gender":"Male","manufacturer":"Dodge","model":"Dakota","year":2002,"vin":"JTDZN3EU7FJ032100","color":"Teal"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Paquito","last_name":"Chappelow","gender":"Male","manufacturer":"Ford","model":"Falcon","year":1967,"vin":"WA1EY94L67D885695","color":"Crimson"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Klarrisa","last_name":"Ryott","gender":"Female","manufacturer":"Mitsubishi","model":"Tredia","year":1988,"vin":"1GD12YEG1FF019807","color":"Teal"}');

INSERT INTO vehicle(vehicle_data) values('{"first_name":"Maurice","last_name":"Minot","gender":"Male","manufacturer":"Acura","model":"Vigor","year":1992,"vin":"3C63DRLL0CG858281","color":"Indigo"}');

Here is a more meaningful illustration of the JSON we are putting away.

{
"first_name":"Austine",
"last_name":"Okill",
"gender":"Polygender",
"manufacturer":"GMC",
"model":"Sierra Hybrid",
"year":2006,
"vin":"5TDBK3EH7BS492643",
"color":"Maroon"
}

Executing a Query

Here is a query that we can use to filter our data according to the manufacturer of the vehicles.

select * from vehicle where vehicle_data->>"$.manufacturer" = 'Ford';

Note: The ->> is shorthand for JSON_EXTRACT() within JSON_UNQUOTE()

Although this query doesn’t have any performance issues with our small dataset, it might if more data is added to our table.

Let’s run the following query to examine the explain plan for this one:

explain select * from vehicle where vehicle_data->>"$.manufacturer" = 'Ford'\G

Note: Although this query doesn’t have any performance issues with our small dataset, it might if more data is added to our table.

Let’s run the following query to examine the explain plan for this one:

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: vehicle

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 10

     filtered: 100

        Extra: Using where

1 row in set, 1 warning (0.0008 sec)

The values of possible keys and key are the parts of the results that we are most interested in. As we can see, these values are NULL, indicating that there are no indexes available for this query.

Adding the Index

We can build an index based on the JSON values to address any potential performance issues with this query. This is how the SQL command would appear:

ALTER TABLE vehicle

    ADD INDEX manufacturer((

        CAST(vehicle_data->>"$.manufacturer" as CHAR(255))

    COLLATE utf8mb4_bin

    ));

Following the execution of this command, the following command can be used to view the definition of the index:

show indexes from vehicle\G

This command should return a list of two indexes, including the index for our primary key and the newly created index.

*************************** 1. row ***************************

        Table: vehicle

   Non_unique: 0

     Key_name: PRIMARY

Seq_in_index: 1

  Column_name: id

    Collation: A

  Cardinality: 9

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

      Visible: YES

   Expression: NULL

*************************** 2. row ***************************

        Table: vehicle

   Non_unique: 1

     Key_name: manufacturer

Seq_in_index: 1

  Column_name: NULL

    Collation: A

  Cardinality: 6

     Sub_part: NULL

       Packed: NULL

         Null: YES

   Index_type: BTREE

      Comment:

Index_comment:

      Visible: YES

   Expression: (cast(json_unquote(json_extract(`vehicle_data`,_utf8mb4\'$.manufacturer\')) as char(255) charset utf8mb4) collate utf8mb4_bin)

At the point when we take a gander at the articulation property of our new list, we can see that the text vehicle_data->>”$.manufacturer” has been replaced with json_unquote(json_extract(vehicle_data,_utf8mb4\’$.manufacturer\’)).

You might be wondering why we chose to create this index using CAST() and COLLATE.

To start with, the capability JSON_UNQUOTE() returns an information sort of LONGTEXT. The information type LONGTEXT can’t be utilized in a file, so we really want to CAST() the outcomes to an information type that can be recorded. In this model, CHAR(255).

Then, we use COLLATE on the grounds that the capabilities used to extricate information (utilized in the WHERE articulation in our question) are collated to utf8mb4_bin. Be that as it may, when we cast a string without utilizing COLLATE, it is cast to utf8mb4_0900_ai_ci. At the point when the examination of what is put away in the list doesn’t match the resemblance of the string in our WHERE proviso, the record won’t get utilized.

Monitoring Our Work

Let’s check the effectiveness of our explain plan by rerunning this command after we’ve created the index:

explain select * from vehicle where vehicle_data->>"$.manufacturer" = 'Ford'\G

The end result ought to resemble this:

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: vehicle

   partitions: NULL

         type: ref

possible_keys: manufacturer

          key: manufacturer

      key_len: 1023

          ref: const

         rows: 2

     filtered: 100

        Extra: NULL

1 row in set, 1 warning (0.0085 sec)

We can see that key and possible key have values now, indicating that this query is using our new index.

To Conclude

Putting away JSON information in a social data set is something engineers have been doing well before a JSON information type existed. The JSON information type permits us to store substantial JSON information and run questions in light of the qualities in the JSON object. By utilizing utilitarian files, we can help the presentation of those questions similarly a list on different information types helps support execution.

Our server support team will be available whenever you need them to recover your data if you are having any problems with MySQL.