E-commerce Order Denormalization

advanced

Orders table with 5M rows. Order listing requires joining customers and products tables (800ms). Denormalize by adding customer_name and product_name to orders.

-- Add redundant columns

ALTER TABLE orders

ADD COLUMN customer_name VARCHAR(100),

ADD COLUMN product_name VARCHAR(200);

-- Populate existing data

UPDATE orders o

SET

customer_name = (SELECT name FROM customers c WHERE c.customer_id = o.customer_id),

product_name = (SELECT name FROM products p WHERE p.product_id = o.product_id);

-- Create trigger for consistency

CREATE TRIGGER sync_order_customer_name

AFTER UPDATE OF name ON customers

FOR EACH ROW

BEGIN

UPDATE orders SET customer_name = NEW.name WHERE customer_id = NEW.customer_id;

END;

-- Query before (with joins)

SELECT o.order_id, c.name, p.name, o.total

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

JOIN products p ON o.product_id = p.product_id

LIMIT 50;

-- Execution time: 847ms

-- Query after (no joins)

SELECT order_id, customer_name, product_name, total

FROM orders

LIMIT 50;

-- Execution time: 45ms

Query time: 847ms -> 45ms (18.8x faster)

Daily queries: 50,000

Time saved: 40,100 seconds/day (11.1 hours)

Write overhead: 4.5 seconds/day

Net benefit: 40,095 seconds/day

Denormalization eliminates 2 joins by storing customer_name and product_name directly in orders. Reads are 18x faster, writes are 10x slower, but writes happen 500x less frequently. Triggers maintain consistency when names change.

All

erDiagram

ORDERS_NORMALIZED {