DEV Community

Cover image for Indexing JSON in MySQL - aarondfrancis
Knowledge From Twitter
Knowledge From Twitter

Posted on • Edited on • Originally published at infotweets.com

Indexing JSON in MySQL - aarondfrancis

Original Tweet From @aarondfrancis


People often say that you can't index JSON with MySQL, but that's not entirely accurate.

It's totally possible to index JSON columns with MySQL!

It's kinda tricky, but definitely worth it.

Let's take a look at how...

I've written the whole thing up on the @planetscaledata blog, if you'd rather read it in article form:

Indexing JSON in MySQL

Learn how to index JSON in MySQL with generated columns and functional indexes.

  <div class="color-secondary fs-s flex items-center">
      <img
        alt="favicon"
        class="c-embed__favicon m-0 mr-2 radius-0"
        src="https://planetscale.com/favicon.svg"
        loading="lazy" />
    planetscale.com
  </div>
</div>
Enter fullscreen mode Exit fullscreen mode

.

The reason people say that you can't index JSON in MySQL is because you can't directly index it, you have to do it indirectly.

Other databases have GIN indexes that help with this, but since MySQL doesn't have those you have two options:

• a generated column (MySQL 5.7)
• a functional index (MySQL 8.0.13)

Let's look at generated columns first!

A generated column is like a calculated, computed, or derived column.

MySQL keeps the column up to date for you, based on the expression you give it.

You can pluck any path out of a JSON column and make it a proper, top-level column. Twitter Image 1

Now you can add an index to it just like you would any other column! Boom, you've just indexed JSON in MySQL.

It gets better though (if you can believe that!) Twitter Image 2

The nice part about this is that you don't have to use the new, named column.

MySQL is smart enough to use the index when the expression that matches your generated column.

AdSpace Here!!

This is super helpful if you can't control the access pattern in your application for whatever reason. Twitter Image 3

If you're on MySQL 8.0.13 or later, you can use a "functional index."

It's basically the same thing, but without manually creating the generated column yourself. You just index the expression.

It has a few unfortunate gotchas though, mainly around data types and collations.

Under the hood MySQL is going to use a hidden generated column for this functional index.

But! Since MySQL just infers the data type and collation from your expression, you have to help it match your index, otherwise it won't be used.

(Honestly this part is a pain.) Twitter Image 4

When you control the generated column yourself, it's a bit easier, but then you have the generated column hanging around, which you may not like.

Either method is totally acceptable, which one you use is up to you!

AdSpace Here!!

Go forth and index JSON with confidence.

If you enjoyed this, boy do I have good news for you. There's a lot more where this came from.

Click Here For More Posts Like This https://www.knowledge-twitter.com

Top comments (0)