Indexing JSON in MySQL - aarondfrancis
Tags: webdev, mysql, sql, tutorial
Published: 2022-10-12 17:47:06

####   Original Tweet From  [@aarondfrancis](https://twitter.com/aarondfrancis)
{% embed https://twitter.com/aarondfrancis/status/1577325434248757249 %}
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](https://twitter.com/planetscaledata) blog, if you'd rather read it in article form: {% embed https://planetscale.com/blog/indexing-json-in-mysql %}.

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](https://pbs.twimg.com/media/FePDHZ4XgAQe9Z5.jpg) 

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](https://pbs.twimg.com/media/FePDQnpXkAQEfky.jpg) 

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](https://pbs.twimg.com/media/FePB9sFWIAMYF93.jpg) 

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](https://pbs.twimg.com/media/FePFfXBWIAEczYS.jpg) 

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. 

{% embed https://twitter.com/aarondfrancis/status/1545886078581751811 %} 



##### Click [Here](https://www.knowledge-twitter.com/?utm_source=dev.to)  For More Posts Like This [https://www.knowledge-twitter.com](https://www.knowledge-twitter.com/?utm_source=dev.to)