Indexing MySQL JSON Data


“MySQL’s JSON data type is great! But how do you index the JSON data?” I was recently presenting at the CakePHP Cakefest Conference and was asked that very question. And I had to admit I had not been able to play, er, experiment with the JSON datatype to that level. Now I have and it is fairly easy.

1. Create a simple table
mysql> desc colors;
+--------------+----------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------------------+
| popular_name | char(10) | YES | | NULL | |
| hue | json | YES | | NULL | |
+--------------+----------+------+-----+---------+-------------------+
2 rows in set (0.00 sec)

2. Add in some data
INSERT INTO `colors` VALUES ('red','{\"value\": \"f00\"}'),('green','{\"value\": \"0f0\"}'),('blue','{\"value\": \"00f\"}'),('cyan','{\"value\": \"0ff\"}'),('magenta','{\"value\": \"f0f\"}'),('yellow','{\"value\": \"ff0\"}'),('black','{\"value\": \"000\"}');

3. SELECT some data
Use the jsb_extract function to efficiently search for the row desired.
mysql> select jsn_extract(hue, '$.value') from colors where jsn_extract(hue, '$.value')="f0f";
+-----------------------------+
| jsn_extract(hue, '$.value') |
+-----------------------------+
| "f0f" |
+-----------------------------+
1 row in set (0.00 sec)

But how efficient is that? Turns out we end up doing a full table scan.

mysql> explain select jsn_extract(hue, '$.value') from colors where jsn_extract(hue, '$.value')="f0f";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | colors | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

4 Add a VIRTUAL column to index quickly
mysql> ALTER TABLE colors ADD value_ext char(10) GENERATED ALWAYS AS (jsn_extract(hue, '$.value')) VIRTUAL;
This will add a virtual column from the value data in the hue column.

5 Index the New Column
mysql> CRATE INDEX value_ext_index ON colors(value_ext);

Now the EXPLAIN shows us that we are more efficient.
mysql> explain select jsn_extract(hue, '$.value') from colors where value_ext="f0f";
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | colors | NULL | ref | value_ext_index | value_ext_index | 11 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

6 Responses to Indexing MySQL JSON Data

  1. […] PlanetMySQL Voting: Vote UP / Vote DOWN Full article:Indexing MySQL JSON Data […]

  2. Kerem says:

    actually, mysql’s json support is not satisfying. e.g: i cannot do this;
    SELECT json_array(id,name,old) AS array_data FROM users;
    SELECT json_object(id,name,old) AS object_data FROM users;
    SELECT json_object(id AS u_id,name AS u_name, old AS u_old) AS object_data FROM users;
    and that queries should give result like;
    array_data
    [1,”kerem”,30]
    [2,”john”,35]

    object_data
    {“id”:1,”name”:”kerem”,”old”:30}
    {“id”:2,”name”:”john”,”old”:35}
    object_data
    {“u_id”:1,”u_name”:”kerem”,”u_old”:30}
    {“u_id”:2,”u_name”:”john”,”u_old”:35}

    off course we can do this a few CONCAT call but it’s really tiresome. see:
    https://github.com/qeremy/oppa/blob/master/Oppa/Database/Query/Builder.php#L206

    // json_array() and json_object() are just a trivial stuff for examples

  3. Rick Hillegas says:

    Hi Kerem,

    I don’t think that I understand your comment. Are you saying that you don’t like the current behavior of the MySQL JSN_ARRAY() and JSN_OBJECT() functions? Note that they will be renamed to be JSON_ARRAY() and JSON_OBJECT() in the next release candidate. With the current lab release, you can do the following:

    create table users( id int, name varchar(50), old int );
    insert into users values
    ( 1, ‘kerem’, 30 ),
    ( 2, ‘john’, 35 );

    select jsn_array( id, name, old ) from users;

    select jsn_object
    (
    ‘u_id’, id,
    ‘u_name’, name,
    ‘u_old’, old
    ) from users;

    Those SELECTs yield pretty much the results you want. Please help me understand what you think is missing.

    Thanks,
    -Rick

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s