Indexing JSON in MySQL - aarondfrancis

Original Tweet From @aarondfrancis

Aaron Francis

@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...

15:51 PM - 04 Oct 2022

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>

.

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.

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!)

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.

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.)

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.

Aaron Francis

@aarondfrancis

Now I'm going straight to the source: I printed and bound two chapters of the MySQL docs!

It's a move being described as "the nerdiest thing I've ever heard" (@hollylawly) and "a really smart, good idea." (me)

21:42 PM - 09 Jul 2022

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