JSON document fast lookup with MySQL 5.7

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.

JSON document fast lookup

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 the 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:

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.

Share this post

Comments (4)

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.