Materialized Views
MySQL 8.4 has no materialized views, so you build a summary table yourself and choose how to refresh it.
CREATE TABLE summary_new (...) AS SELECT ...; RENAME TABLE summary TO summary_old, summary_new TO summary;A materialized view stores the result of a query and refreshes it on a schedule or on demand. The MySQL manual answers the question about support in one word: no. There is no CREATE MATERIALIZED VIEW in MySQL 8.4, and an ordinary view caches nothing. What you build instead is a plain table plus a refresh job that you own, and the interesting part is the refresh strategy.
Full rebuild
Build the snapshot into a new table, then swap it in with RENAME TABLE, which is atomic across several renames — readers see the old table or the new one, never a half-filled one. Cost is proportional to the whole dataset. Simple, and the right default while a rebuild still takes seconds.
Incremental refresh
Only reprocess rows changed since the last run, using a watermark such as updated_at. Cheap, but it needs a column that always moves forward, and it cannot see deleted rows or back-dated edits. Pair it with a nightly full rebuild to correct drift.
Trigger maintenance
Triggers keep the summary current inside the writing transaction. The cost moves onto every write, and every writer touching the same product or day contends on one summary row.
Scheduling
The Event Scheduler can run a refresh inside the server with CREATE EVENT; external cron is easier to monitor and alert on. Either way, store the build time in the table and show it, so nobody mistakes the number for live data.
Example
-- MySQL has no CREATE MATERIALIZED VIEW. This is the hand-built equivalent:
-- build beside the live table, then swap atomically.
DROP TABLE IF EXISTS revenue_by_day_new;
CREATE TABLE revenue_by_day_new (
day DATE NOT NULL,
product_id INT NOT NULL,
orders INT NOT NULL,
revenue_cents BIGINT NOT NULL,
built_at DATETIME NOT NULL, -- so readers can see how stale this is
PRIMARY KEY (day, product_id)
) ENGINE=InnoDB AS
SELECT DATE(o.placed_at) AS day,
i.product_id AS product_id,
COUNT(DISTINCT o.id) AS orders,
SUM(i.qty * i.unit_cents) AS revenue_cents,
NOW() AS built_at
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE o.status = 'paid'
GROUP BY DATE(o.placed_at), i.product_id;
-- RENAME TABLE is atomic across all three renames: no reader ever sees
-- a missing or partially built revenue_by_day.
RENAME TABLE revenue_by_day TO revenue_by_day_old,
revenue_by_day_new TO revenue_by_day,
revenue_by_day_old TO revenue_by_day_new;
DROP TABLE revenue_by_day_new; -- the previous snapshotWhen to use it
- Serve a dashboard that aggregates millions of order rows without re-scanning them on every page load.
- Precompute a nightly per-customer lifetime-value table that reporting tools join against.
- Freeze month-end figures so a late edit to an old order cannot change a published report.
- Keep exact stock counts updated by trigger while nightly rebuilds correct any drift.
More examples
Incremental refresh on a watermark, scheduled as an event
Reprocesses only the days that changed, so a 15-minute cadence stays cheap on a large orders table. The row alias form (the derived table aliased AS new, then new.orders in the UPDATE clause) is the current way to refer to the incoming row; the older VALUES(orders) spelling does the same thing but is deprecated in MySQL 8.4. This strategy cannot see deletes, so schedule a full rebuild nightly as well.
DELIMITER //
CREATE EVENT IF NOT EXISTS refresh_revenue_by_day
ON SCHEDULE EVERY 15 MINUTE
STARTS CURRENT_TIMESTAMP
DO
BEGIN
INSERT INTO revenue_by_day (day, product_id, orders, revenue_cents, built_at)
SELECT * FROM (
SELECT DATE(o.placed_at) AS day,
i.product_id AS product_id,
COUNT(DISTINCT o.id) AS orders,
SUM(i.qty * i.unit_cents) AS revenue_cents,
NOW() AS built_at
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE o.status = 'paid'
-- only days touched since the last snapshot
AND o.updated_at >= COALESCE((SELECT MAX(built_at) FROM revenue_by_day),
'1970-01-01')
GROUP BY DATE(o.placed_at), i.product_id
) AS new
ON DUPLICATE KEY UPDATE
orders = new.orders,
revenue_cents = new.revenue_cents,
built_at = new.built_at;
END //
DELIMITER ;
SET GLOBAL event_scheduler = ON; -- nothing runs until this is onTrigger-maintained running totals
The summary is exact and current the moment the inserting transaction commits, which is what you want for stock counts or credit limits. The price is real: every insert now also writes one product_stats row, and two orders for the same product serialise on it. You also need matching AFTER UPDATE and AFTER DELETE triggers, or the totals drift.
CREATE TABLE product_stats (
product_id INT PRIMARY KEY,
units_sold BIGINT NOT NULL DEFAULT 0,
revenue_cents BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
DELIMITER //
CREATE TRIGGER order_items_ai_stats
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
INSERT INTO product_stats (product_id, units_sold, revenue_cents)
VALUES (NEW.product_id, NEW.qty, NEW.qty * NEW.unit_cents) AS new
ON DUPLICATE KEY UPDATE
units_sold = product_stats.units_sold + new.units_sold,
revenue_cents = product_stats.revenue_cents + new.revenue_cents;
END //
DELIMITER ;Publishing staleness with the data
A hand-built materialized view fails quietly: the table keeps answering queries with yesterday's numbers after the refresh job dies. Selecting the build time alongside the figures, and running a staleness check from monitoring, turns that silent failure into a visible one.
-- Never hand out a snapshot number without its age.
SELECT day,
SUM(revenue_cents) / 100 AS revenue,
MAX(built_at) AS snapshot_taken_at,
TIMESTAMPDIFF(MINUTE, MAX(built_at), NOW()) AS minutes_stale
FROM revenue_by_day
WHERE day >= CURDATE() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day;
-- Alert when the refresh job has stopped running.
SELECT 'revenue_by_day is stale' AS problem
FROM revenue_by_day
HAVING MAX(built_at) < NOW() - INTERVAL 1 HOUR;
Discussion