Use a Stored Column for Indexing Values Stored in JSON [MySQL Tip]

Managing and querying JSON data in MySQL databases has become common in modern web applications. To improve performance when querying JSON values, you can create a stored column that extracts specific values and indexes them.


Table Definition


Consider a scenario where you have a table named "orders" that stores various details about customer orders, including a JSON column called "data" that holds additional information related to each order. To improve performance when querying by a specific order number, you can create a stored column that extracts the order number from the JSON data and indexes it.

Let's take a look at the table definition:


CREATE TABLE `orders`
(
    `id`           INT UNSIGNED                                            NOT NULL,
    `data`         JSON                                                    NOT NULL,
    `order_number` VARCHAR(20) GENERATED ALWAYS AS (`data` ->> '$.number') NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `orders_order_number_idx` (`order_number`)
);


In this example, the table orders has columns for id, data, and order_number. The order_number column is defined as a stored column using the GENERATED ALWAYS AS syntax, with the expression (data ->> '$.number') to extract the value of the number key from the JSON data.

By creating an index on the order_number column using INDEX, MySQL optimizes the query performance when searching and filtering based on order numbers.

With the table and index set up, you can execute queries like:


SELECT * FROM `orders` WHERE `order_number` = '123456';



Conclusion


Using a stored column for indexing JSON values can significantly improve query performance, especially with large datasets. Remember to carefully design and maintain indexes based on your application's needs to balance performance and write operations.

In conclusion, leveraging a stored column for indexing JSON values in MySQL can enhance query performance and provide the flexibility of working with JSON data within your database schema.

Hope you have enjoyed this short bright dev tip! Check our repository.

---

By Maciej Nawrocki, Senior Backend Developer and Patryk Szlagowski, Senior Backend Developer at Bright Inventions.