When to Denormalize

Duplicating a fact to make a read cheap, the MySQL tools for holding that copy, and the drift you sign up for.

Syntaxcol_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]

Denormalization is storing the same fact in more than one place on purpose, so that a read costs less. It is a trade rather than an improvement: you pay for read latency with write complexity and the permanent possibility that the two copies disagree.

When it is justified

After measurement, not before. Run EXPLAIN, confirm the indexes exist and are being used, and confirm the query is actually hot. The cases that survive that test are usually a count rendered on every page view, an aggregate over a table too large to scan per request, or a dashboard rollup.

MySQL 8.4 has no materialized views — a view is expanded into the query at execution time — so a summary table maintained by a job, a trigger or the application is the substitute.

How to hold the copy

Prefer the forms MySQL maintains for you. A STORED generated column is computed by the server on every write and cannot drift, and it can be indexed. Next best is a counter updated inside the same transaction as the row that changed it. Last is a rollup table rebuilt on a schedule, which is allowed to be stale as long as it says when it was built.

The cost you take on

Every write path must now maintain both copies, and anything that bypasses the path — a bulk load, a manual fix, a restored backup, a second service — makes them disagree. Write the reconciliation query on the same day you add the column. A hot counter also concentrates locks: every comment on a popular post updates the same posts row.

Example

Example · sql
-- A comment count rendered on every listing page. Counting per request is
-- the thing being avoided; the column is the duplicate we take on.
ALTER TABLE posts
  ADD COLUMN comment_count INT UNSIGNED NOT NULL DEFAULT 0;

DELIMITER //

CREATE TRIGGER trg_comments_ai AFTER INSERT ON comments
FOR EACH ROW
BEGIN
  UPDATE posts SET comment_count = comment_count + 1
  WHERE id = NEW.post_id;
END//

CREATE TRIGGER trg_comments_ad AFTER DELETE ON comments
FOR EACH ROW
BEGIN
  UPDATE posts SET comment_count = comment_count - 1
  WHERE id = OLD.post_id;
END//

DELIMITER ;

-- Write this NOW, not when someone questions the numbers. Anything that
-- deletes comments without firing the trigger -- a cascade from posts, a
-- bulk purge run with foreign_key_checks off, a restore -- lands here.
SELECT p.id, p.comment_count AS stored, COUNT(c.id) AS actual
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.comment_count
HAVING stored <> actual;

When to use it

  • Show a comment or follower count on a listing page without counting rows per request.
  • Serve a dashboard from a nightly rollup instead of aggregating millions of order lines live.
  • Store a computed line total as a STORED generated column so it can be indexed and summed cheaply.
  • Detect and repair a counter column that has drifted away from the rows it counts.

More examples

The copy MySQL maintains for you

Reach for a STORED generated column whenever the duplicate is a pure function of other columns in the same row. It is the only form of denormalization with no drift risk, because there is no second write path to forget. The expression must be deterministic — no NOW(), no subqueries, no stored functions — which rules out anything depending on other tables.

Example · sql
CREATE TABLE invoice_lines (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id  BIGINT UNSIGNED NOT NULL,
  qty         INT UNSIGNED NOT NULL,
  unit_cents  INT UNSIGNED NOT NULL,
  tax_bp      INT UNSIGNED NOT NULL,   -- basis points

  -- Derived, stored, and indexable. The server recomputes it on every
  -- insert and update, so no write path can forget it and it cannot drift.
  -- VIRTUAL (the default) would compute it per read instead; STORED is the
  -- denormalized choice, trading disk for read cost.
  total_cents BIGINT UNSIGNED
    GENERATED ALWAYS AS (qty * unit_cents * (10000 + tax_bp) DIV 10000) STORED,

  PRIMARY KEY (id),
  KEY idx_lines_invoice_total (invoice_id, total_cents)
) ENGINE=InnoDB;

SELECT invoice_id, SUM(total_cents) AS invoice_total
FROM invoice_lines
WHERE invoice_id = 900123
GROUP BY invoice_id;

A rollup table that admits it is stale

The standard answer to "this dashboard query scans four million rows". Because MySQL 8.4 has no materialized views, this batch job is the materialized view. REPLACE INTO keyed on (sale_date, product_sku) makes a re-run for a given day idempotent, which is what you need when yesterday's orders get corrected today.

Example · sql
CREATE TABLE daily_sales (
  sale_date    DATE NOT NULL,
  product_sku  VARCHAR(32) NOT NULL,
  units        INT UNSIGNED NOT NULL,
  revenue_cents BIGINT UNSIGNED NOT NULL,
  -- The freshness stamp is part of the contract, not decoration. A report
  -- that cannot say how old it is will be trusted when it should not be.
  computed_at  DATETIME NOT NULL,
  PRIMARY KEY (sale_date, product_sku)
) ENGINE=InnoDB;

-- Rebuild one day, idempotently. Safe to re-run after a late correction.
REPLACE INTO daily_sales (sale_date, product_sku, units, revenue_cents, computed_at)
SELECT DATE(o.placed_at),
       oi.product_sku,
       SUM(oi.qty),
       SUM(oi.qty * oi.price_cents),
       NOW()
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.placed_at >= '2026-09-16' AND o.placed_at < '2026-09-17'
GROUP BY DATE(o.placed_at), oi.product_sku;

Repairing counters without a maintenance window

The operational half of owning a denormalized column, and the part usually skipped. Batching by primary key range keeps each transaction short; the WHERE on the outer UPDATE means only genuinely wrong rows are written, so a repair pass over a clean table costs almost nothing and can run regularly.

Example · sql
-- Drift is not hypothetical. Fix it in bounded batches so a single long
-- UPDATE does not hold locks across the whole posts table.
UPDATE posts p
JOIN (
  SELECT p.id, COUNT(c.id) AS actual
  FROM posts p
  LEFT JOIN comments c ON c.post_id = p.id
  WHERE p.id BETWEEN 1 AND 10000        -- advance this window per batch
  GROUP BY p.id
) fixed ON fixed.id = p.id
SET p.comment_count = fixed.actual
WHERE p.comment_count <> fixed.actual;

-- Then schedule the detection query from the main example to run nightly
-- and alert on any row it returns, rather than waiting for a bug report.

Related

sql normalizationsql generated columnssql triggerssql viewssql materialized views

Discussion

  • Be the first to comment on this lesson.