EmergencyEMERGENCY? Get 24/7 Help Now!

JSON document fast lookup with MySQL 5.7

 | March 7, 2016 |  Posted In: JSON, MySQL

PREVIOUS POST
NEXT POST

JSON document fast lookup with MySQL 5-7In this blog post, we’ll discuss JSON document fast lookup with MySQL 5.7.

Recently I attended Morgan Tocker’s talk on MySQL 5.7 and JSON at FOSDEM, and I found it awesome.

I learned some great information from the talk. Let me share one of them here: a very useful trick if you plan to store JSON documents in your MySQL database and want to retrieve the documents from some attribute’s values. So let’s look at how to do JSON document fast lookup with MySQL 5.7!

In this short example, I show you how we can speed up this type of search using JSON functions and virtual columns.

This our test table:

We can see the data length is almost 230M:

As an example here is one record (the data is coming from https://github.com/zemirco/sf-city-lots-json):

Now let’s try to find all records where the street is “BEACH”. “Street” is part of the array attribute properties.

As you can see, we perform a full table scan to achieve this.

With MySQL, we have the possibility of using virtually generated columns. Let’s create one for the streets:

I use “json_unquote()” to avoid to add the JSON string quotes in the column, and later in the index.

You can verify the size of the table on disk, and you will see this doesn’t increase (as it’s a virtual column).

Even if we can now use the “street” column in the search, that won’t help. We still need to add an index on it:

And now we can see that the size is larger, because we have added the size of the index:

Now we can try to run the query like this:

Let’s have a look at the Query Execution Plan:

And finally we can verify this in the statistics available in sys schema:

As you can see, this is very fast. If you already know how you want to retrieve data out of your JSON document, it’s very easy to add such indexes in MySQL.

PREVIOUS POST
NEXT POST
Frederic Descamps

Frédéric joined Percona in June 2011, he is an experienced Open Source consultant with expertise in infrastructure projects as well in development tracks and database administration. Frédéric is a believer of devops culture.

4 Comments

Leave a Reply