MySQL Generated Columns: Speed Up JSON Queries and Clean Up Your Schema

The 2 AM Database Meltdown

It was 2 AM when the monitoring alerts hit my phone. Our production database CPU had spiked to 99%, and API response times were dragging at 10 seconds instead of the usual 200ms. I checked the slow query logs and found a mess. We were filtering a 5GB table with 2.5 million rows using business logic buried deep inside a JSON column.

The query looked like this:

SELECT * FROM orders WHERE JSON_EXTRACT(order_details, '$.status') = 'shipped';

Because MySQL cannot natively index a specific path inside a JSON document, every request forced a full table scan. The engine had to parse the JSON for every single row, every time. This is a common performance killer, but it is exactly what Generated Columns were designed to fix.

Virtual vs. Stored: Two Ways to Optimize

MySQL provides two strategies for handling generated data. Choosing the right one determines whether your system scales smoothly or hits a wall as your data grows.

Virtual Generated Columns

A Virtual column is the default option. It does not take up extra space on your disk. Instead, MySQL calculates the value on the fly whenever you read the row. You might worry that recalculating values is slow, but there is a major advantage: you can index a Virtual column. When you do this, MySQL stores the index values physically. You get the speed of a standard index without doubling the storage needed for the actual column data.

Stored Generated Columns

A Stored column calculates the value during an INSERT or UPDATE and writes the result to the disk. It acts like a regular column but remains managed by the database. This is the better choice for extremely complex CPU-intensive calculations. It is also helpful if you use legacy reporting tools that cannot interpret virtual fields.

Comparing the Two Approaches

The Recommended Strategy

For about 90% of production workloads, Virtual Generated Columns are the smarter choice. They allow you to index JSON fields without ballooning the size of your .ibd files. Keeping your disk footprint small is vital for maintaining fast backup speeds and staying within your memory buffer pool limits.

I recently migrated a legacy CSV dataset into a JSON-based architecture. To handle the initial data import, I used toolcraft.app/en/tools/data/csv-to-json. It runs entirely in the browser, so no sensitive data leaves your machine. Once the data was structured in JSON, I added Virtual columns to make the key fields searchable.

Implementation: Optimizing Your JSON

Let’s look at a practical example. Suppose we have a users table where profile info is stored in JSON, and we need to filter by city frequently.

Step 1: Define the Virtual Column

Instead of relying on raw JSON, we define a virtual column that extracts the city string.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_data JSON,
    -- Extracting the city as a virtual field
    user_city VARCHAR(100) GENERATED ALWAYS AS (profile_data->>"$.address.city") VIRTUAL
);

The ->> operator is essential here. It is shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)). This ensures you get a clean string like “Chicago” instead of a quoted JSON value like “\”Chicago\””.

Step 2: Add the Index

The virtual column itself doesn’t fix the speed issue. You must add the index to stop the full table scans.

CREATE INDEX idx_user_city ON users(user_city);

Step 3: Automate Business Logic

Generated columns also prevent “data drift,” where your application and database disagree on a value. For instance, you can calculate a final price automatically:

ALTER TABLE products 
ADD COLUMN final_price DECIMAL(10,2) 
GENERATED ALWAYS AS (base_price - (base_price * discount_percent / 100)) STORED;

In this scenario, I used STORED. This allows us to run financial reports across millions of rows without forcing the CPU to redo the math for every single row during the export.

Verifying the Results

After implementing the virtual column and index during that 2 AM incident, I ran an EXPLAIN on the query. The results were night and day. The access type shifted from a full table scan (ALL) to a ref lookup using idx_user_city. Execution time dropped from 8 seconds to just 1.2 milliseconds. The RDS CPU load immediately fell back to 15%, and I finally got some sleep.

Key Takeaways

Stop treating JSON as a black box if you are building modern apps with MySQL. Use Virtual Generated Columns to expose the fields you query most often. This keeps your application code clean by removing messy JSON_EXTRACT calls from your ORM. Most importantly, it keeps your database fast. Use Virtual for indexing and Stored for heavy computational lifting.