You're Paying a Huge Performance Tax on Your JSON Columns. Here's the One-Line Fix.

You stored it in JSON for flexibility. That flexibility is now costing you full table scans on every filter. Virtual columns are the escape hatch you didn't know existed.

You know the moment. The product manager is changing requirements every other day, and you think: "I'll just throw this into a JSON column for now and clean it up later."

Later never comes. The JSON column stays. The table grows to 5 million rows. And then one morning, your monitoring dashboard turns red.

This is The JSON Trap. And almost every backend engineer walks into it at least once.

MySQL is completely blind to what's inside your JSON

Consider this classic setup — a products table with a flexible attributes column:

CREATE TABLE products (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    attributes JSON
);

A few weeks pass. Marketing wants product filtering by color. You write this:

SELECT * FROM products
WHERE attributes->>'$.color' = 'matte_black';

Works perfectly on localhost with 100 rows. On production with 5 million rows? Run EXPLAIN and brace yourself:

type: ALL means MySQL is opening every single row, deserializing the JSON, extracting the key, and comparing it. You cannot put a standard B-Tree index on a JSON key path. The database is flying blind on every filter.

Virtual columns: zero disk cost, full index power

A Virtual Generated Column is MySQL's answer to this exact problem. It doesn't store anything on disk. It exposes a computed value — derived from an expression — as if it were a real column. And crucially: you can index it.

Step 1 — expose the JSON key as a virtual column:

ALTER TABLE products
ADD COLUMN product_color VARCHAR(50)
GENERATED ALWAYS AS (attributes->>'$.color') VIRTUAL;

Step 2 — index it:

ALTER TABLE products ADD INDEX idx_product_color (product_color);

That's it. Now run your original query:

SELECT * FROM products
WHERE product_color = 'matte_black';

The connection to functional indexes (this will click something)

If you've read about MySQL 8.0's functional indexes — like INDEX ((LOWER(email))) — this pattern should look familiar. That's not a coincidence.

Three rules before you go index everything