Imagine having a tool that can automatically detect JPA and Hibernate performance issues.
Hypersistence Optimizer is that tool!
In this article, I’m going to explain how we can index JSON columns when using MySQL.
While other relational database systems provide GIN (Generalized Inverted Index) indexes, MySQL allows you to index a virtual column that mirrors the JSON path expression you are interested in indexing.
Let’s assume we have the following database book table:
Not that the key attribute references the book_title_idx index we’ve just created, meaning that the SQL query is not using the index to filter the book records, which is also confirmed by the rows_examined_per_scan attribute since a single index row was scanned.
Updating the indexed JSON attribute
If you update the indexed JSON attribute:
properties = JSON_SET(
'High-Performance Java Persistence, 2nd edition'
isbn = '978-9730228236'
You’ll see that the title virtual column is updated accordingly:
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
JSON column types can be very useful, especially if you’re implementing an audit log.
While adding a GIN index on the JSON columns is not allowed in MySQL, you can define a virtual column and add an index to it.
However, you need to make sure that the JSON attribute you’re using for indexing is highly selective, as otherwise, the index might not be used by the database engine when generating the SQL execution plan.