TLDR: Trying to optimize JSON queries in MYSQL, make use of stored generated columns Ran into a situation where I had a table with loads and loads of records that included a meta JSON column, ideally the fields in the JSON column were queried using JSON_EXTRACT meta JSON_EXTRACT models.sequelize.where(
  models.sequelize.fn(
      'JSON_EXTRACT', models.sequelize.col('meta'), models.sequelize.literal('$.type')
  ),
  Op.eq,
  query.type
) models.sequelize.where(
  models.sequelize.fn(
      'JSON_EXTRACT', models.sequelize.col('meta'), models.sequelize.literal('$.type')
  ),
  Op.eq,
  query.type
) JSON columns are useful for flexible data models, but they have limitations; queries on JSON fields can’t make use of indexes. As the number of records increased, the limitations of not being able to index a JSON column began to become apparent, and queries started to become considerably slower. JSON STORED GENERATED COLUMN STORED GENERATED COLUMN A generated column in MySQL is a column where the values are computed from the values of other columns in the same row. A generated column can be either virtual or stored Virtual column - Data is computed every time the data is queried; data isn’t stored in the DB
Stored generated column - Data is computed when the source columns are created/updated and stored on disk, similar to how a normal column would be stored Virtual column - Data is computed every time the data is queried; data isn’t stored in the DB Stored generated column - Data is computed when the source columns are created/updated and stored on disk, similar to how a normal column would be stored Virtual Column vs Stored-generated column Virtual column

Stored Generated column



Can query against

Yes

Yes



Can index

No

Yes



Uses a lot of space

No

Yes



Columns are stored physically

No

Yes



Automatic updates

No, data is re-computed every time the column is queried

Yes, the column is updated automatically



Created or updated directly

No

No Virtual column

Stored Generated column



Can query against

Yes

Yes



Can index

No

Yes



Uses a lot of space

No

Yes



Columns are stored physically

No

Yes



Automatic updates

No, data is re-computed every time the column is queried

Yes, the column is updated automatically



Created or updated directly

No

No Virtual column

Stored Generated column Virtual column Virtual column Stored Generated column Stored Generated column Can query against

Yes

Yes Can query against Can query against Yes Yes Yes Yes Can index

No

Yes Can index Can index No No Yes Yes Uses a lot of space

No

Yes Uses a lot of space Uses a lot of space No No Yes Yes Columns are stored physically

No

Yes Columns are stored physically Columns are stored physically No No Yes Yes Automatic updates

No, data is re-computed every time the column is queried

Yes, the column is updated automatically Automatic updates Automatic updates No, data is re-computed every time the column is queried No, data is re-computed every time the column is queried Yes, the column is updated automatically Yes, the column is updated automatically Created or updated directly

No

No Created or updated directly Created or updated directly No No No No Migration await queryInterface.sequelize.query(`
   ALTER TABLE customer ADD COLUMN type VARCHAR(30)
    AS (JSON_UNQUOTE(JSON_EXTRACT(meta, '$.type'))) STORED,
   ADD INDEX type_idx (type); `); await queryInterface.sequelize.query(`
   ALTER TABLE customer ADD COLUMN type VARCHAR(30)
    AS (JSON_UNQUOTE(JSON_EXTRACT(meta, '$.type'))) STORED,
   ADD INDEX type_idx (type); `); This migration script adds a type column to the customer table, it then tells MySQL to compute the value by extracting it from the meta.type and store STORED as a generated column.  ADD INDEX type_idx (type) adds an index on the field to allow for faster queries type customer meta.type STORED ADD INDEX type_idx (type) Benefits Columns are indexable
Queries are faster than querying JSON columns
You can query against the stored column as you’d query any other column Columns are indexable Queries are faster than querying JSON columns You can query against the stored column as you’d query any other column Cons Makes use of a little more space but as someone I know would say SPACE IS CHEAP Makes use of a little more space but as someone I know would say SPACE IS CHEAP SPACE IS CHEAP Conclusion In conclusion, if there’s a need to query JSON columns at scale, extract the fields most frequently queried into stored generated columns to take advantage of the performance boosts indexing provides

Arweave

Tracking Mongoose Query Times with a Few Lines of Code

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

The Secret to Faster JSON Queries in MySQL: Generated Columns

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Tracking Mongoose Query Times with a Few Lines of Code

Tracing Go’s Garbage Collection Journey: Reference Counting, Tri-Color, and Beyond

Unlocking PR: How Businesses Can Conquer Their Publicity Phobias

Web3’s Real Problem Isn’t Tech. It’s Distribution.

The Noonification: Reduce Javascript: Master the Basics (1/11/2023)

Tracking Mongoose Query Times with a Few Lines of Code

Tracing Go’s Garbage Collection Journey: Reference Counting, Tri-Color, and Beyond

Unlocking PR: How Businesses Can Conquer Their Publicity Phobias

Web3’s Real Problem Isn’t Tech. It’s Distribution.

The Noonification: Reduce Javascript: Master the Basics (1/11/2023)

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps