Boost MySQL Join Performance with Wide Tables and JSON Virtual Columns

In e‑commerce fulfillment, operators often need to export order, product, warehouse, and logistics data that reside in different tables, requiring join queries. When the order volume is large, these joins become slow, and a sudden traffic spike can crash the system.

1. Wide‑Table (Field Redundancy) Approach

A wide table duplicates required fields into a single table, eliminating the need for joins. While it satisfies business needs, it violates normal MySQL design principles and has drawbacks:

Adding or removing columns may lock the table, causing problems under high concurrency.

The relationship between tables becomes unclear, making maintenance difficult.

2. MySQL JSON Column Approach

Since MySQL 5.7.8, a native JSON data type validates stored JSON documents and allows fast extraction of elements.

<code>CREATE TABLE `my_test` (
    `id` INT UNSIGNED NOT NULL,
    `data` JSON NOT NULL,
    PRIMARY KEY (`id`)
);</code>

Insert JSON data:

<code>INSERT INTO my_test VALUES (1,'{"num":1,"name":"abc","age":20,"relation":{"borther":{"num":1},"sister":{"num":2}}}');</code>

Query specific fields:

<code>SELECT 
    json_unquote(data->'$.age') AS age,
    json_unquote(data->'$.name') AS name 
FROM my_test;</code>

Without an index, the query remains slow on large datasets. Adding a virtual column and indexing it improves performance:

<code>ALTER TABLE my_test
    ADD COLUMN name VARCHAR(64) AS (json_unquote(data->'$.name')) VIRTUAL;
CREATE INDEX idx_name ON my_test(name);</code>

Conclusion

For simple business scenarios, a wide table can meet requirements, but for complex cases the JSON data type combined with virtual columns and indexes provides a flexible and performant solution. Middleware like MongoDB can also handle dynamic schema changes when needed.