MENU


Denormalization

Denormalization is the deliberate, controlled reintroduction of redundancy into a normalized schema, in order to make specific queries faster at the cost of extra storage and extra work to keep the redundant copies in sync. Normalization (see 1NF through 5NF) optimizes for data integrity and minimizes redundancy; denormalization trades some of that integrity guarantee back for read speed. It is a decision made deliberately and selectively on top of an already-normalized design, not a substitute for normalizing in the first place.

When and Why

Read-heavy workloads – when a table is read thousands of times for every one write, the write-time cost of maintaining redundant data is easily paid back by faster reads.
Expensive joins on the hot path – a query that joins 4-5 normalized tables on every page load can be flattened into fewer joins by copying frequently-needed columns forward.
Reporting and analytics – dashboards and reports typically aggregate over large amounts of historical data that doesn't change; precomputing the aggregate once is far cheaper than recomputing it on every page view.
Avoiding repeated expensive aggregation – a COUNT/SUM/AVG over millions of rows, run on every request, is a common candidate for being precomputed and stored instead.

Techniques

Redundant Columns

Copy a column from the table that owns it into a table that frequently needs it, avoiding a JOIN on the read path. For example, storing a snapshot of customer_name directly on orders instead of always joining to customers. This is often intentional even in normalized schemas for historical/audit reasons – an order should show the name the customer had at the time of the order, not their current name, so this specific case is arguably not even a violation of normalization discipline, just a different entity (an audit snapshot) than it first appears.

When the redundant column genuinely must track the live value (not a snapshot), it has to be kept in sync explicitly – typically with a TRIGGER on the source table that propagates the change.

Summary / Rollup Tables

MySQL has no native materialized view. The standard substitute is an ordinary table that holds precomputed aggregates, refreshed either:

Incrementally, via TRIGGERAn AFTER INSERT/UPDATE/DELETE trigger on the source table updates the summary row with INSERT ... ON DUPLICATE KEY UPDATE. Keeps the summary always current, at the cost of extra work on every write to the source table.
Periodically, via EVENTA MySQL EVENT (the built-in scheduler, see Server Commands) recomputes the summary table on a schedule – e.g. hourly. Cheaper on the write path, but the summary is only as fresh as the last run.

The event scheduler must be turned on with SET GLOBAL event_scheduler = ON; (or event_scheduler=ON in the server config) before any CREATE EVENT will actually run.

Other Techniques

Precomputed/cached values via a GENERATED column (see Table Definitions) for cheap derivations that MySQL computes on write or read without needing a trigger.
Flattening a 1:N relationship into a JSON column when the child rows are always read together with the parent and never queried independently (see JSON).
Duplicate/star-schema style tables purpose-built for reporting, kept separate from the OLTP schema that serves the application, refreshed on a schedule (a lightweight, same-database analogue of an ETL pipeline into a data warehouse).

Tradeoffs

Extra storage for every redundant copy.
Risk of the copies drifting out of sync if a trigger has a bug, is disabled, or an event fails silently – denormalized data is only as correct as the mechanism that maintains it.
Write amplification – one logical write can turn into several physical writes (the source row plus every redundant copy/summary it touches).
More complex schema to reason about – a column's value may no longer have one single place it's defined.

A common middle ground: keep the normalized tables as the single source of truth, and treat every denormalized column or summary table as a disposable, rebuildable cache derived from them – if it's ever suspect, it can be dropped and regenerated from the normalized data rather than trusted blindly. 

-- Normalized source tables (as produced by Third-Normal-Form)
--   customers (customer_id PK, customer_city)
--   orders    (order_id PK, customer_id FK, order_total DECIMAL, created_at)

-- Technique 1: redundant column, kept in sync with a trigger.
-- Adding customer_city directly to orders avoids a JOIN on every order
-- listing query; a trigger keeps it current if a customer's city changes.
ALTER TABLE orders ADD COLUMN customer_city VARCHAR(80) NULL;

DELIMITER $$
CREATE TRIGGER trg_customers_city_sync
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    IF NOT (OLD.customer_city <=> NEW.customer_city) THEN
        UPDATE orders
        SET customer_city = NEW.customer_city
        WHERE customer_id = NEW.customer_id;
    END IF;
END$$
DELIMITER ;

-- Technique 2: summary/rollup table, refreshed on a schedule instead of
-- a materialized view (MySQL has no native equivalent).
CREATE TABLE daily_sales_summary (
    sale_date    DATE PRIMARY KEY,
    order_count  INT UNSIGNED  NOT NULL,
    total_revenue DECIMAL(12,2) NOT NULL
) ENGINE=InnoDB;

SET GLOBAL event_scheduler = ON;

DELIMITER $$
CREATE EVENT ev_refresh_daily_sales_summary
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    REPLACE INTO daily_sales_summary (sale_date, order_count, total_revenue)
    SELECT DATE(created_at), COUNT(*), SUM(order_total)
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL 2 DAY
    GROUP BY DATE(created_at);
END$$
DELIMITER ;

-- A dashboard reads the precomputed table directly instead of aggregating
-- millions of order rows on every page load:
SELECT sale_date, order_count, total_revenue
FROM daily_sales_summary
ORDER BY sale_date DESC
LIMIT 30;