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.
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
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
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';
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.