JSON columns and partitioned tables

Key points

• MySQL stores JSON in a parsed binary form, so extracting a field does not re-parse the whole document. It is genuinely useful for sparse or caller-defined attributes.
• It is not a substitute for columns. A field you filter, join or sort on should be a real column with a real index - JSON is for data the schema legitimately cannot predict.
• You can index inside JSON via a generated column: extract the field into a stored virtual column and index that. The optimiser then uses the index for queries on the JSON path.
• Partitioning splits one table into physical parts by a key, usually a date range. Its main practical value is that dropping an old partition is instant, while DELETE of millions of rows is not.
• Partition pruning only works if the query filters on the partition key. A query without it touches every partition and is slower than an unpartitioned table would have been.
• The hard constraint: every unique key, including the primary key, must contain the partition column. That often forces a composite primary key and is the reason many attempts to add partitioning stall.

Example

-- JSON for genuinely unpredictable attributes.
CREATE TABLE products (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    sku        VARCHAR(32) NOT NULL,        -- real column: filtered on
    price      DECIMAL(10,2) NOT NULL,      -- real column: sorted on
    attributes JSON,                        -- per-category, unpredictable

    -- Index INSIDE the JSON via a generated column.
    brand VARCHAR(64) AS (attributes->>'$.brand') STORED,
    INDEX idx_brand (brand)
);

SELECT * FROM products WHERE brand = 'Acme';        -- uses idx_brand
SELECT * FROM products
WHERE attributes->>'$.brand' = 'Acme';               -- also uses it

SELECT sku, attributes->>'$.colour'  AS colour,
            attributes->'$.sizes'    AS sizes
FROM products
WHERE JSON_CONTAINS(attributes, '"XL"', '$.sizes');  -- no index: scans

---

-- PARTITIONING by month. Note the primary key MUST include the key.
CREATE TABLE events (
    id         BIGINT NOT NULL AUTO_INCREMENT,
    created_at DATE   NOT NULL,
    payload    JSON,
    PRIMARY KEY (id, created_at)        -- forced: must contain created_at
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2026_06 VALUES LESS THAN (TO_DAYS('2026-07-01')),
    PARTITION p2026_07 VALUES LESS THAN (TO_DAYS('2026-08-01')),
    PARTITION p2026_08 VALUES LESS THAN (TO_DAYS('2026-09-01')),
    PARTITION pmax     VALUES LESS THAN MAXVALUE
);

-- The real payoff: instant, and no replication lag spike.
ALTER TABLE events DROP PARTITION p2026_06;
--   vs DELETE FROM events WHERE created_at < '2026-07-01';
--   which writes millions of undo records and floods the binary log.

-- Pruning requires the partition key in the WHERE clause:
EXPLAIN SELECT * FROM events WHERE created_at = '2026-08-03';
--   partitions: p2026_08              <- one partition read
EXPLAIN SELECT * FROM events WHERE id = 12345;
--   partitions: p2026_06,p2026_07,... <- ALL of them. Slower than no
--                                        partitioning at all.

Index JSON through a generated column, and only partition when queries filter on the partition key - otherwise it is slower than not partitioning.