Rolling Summary Tables
Events can pre-aggregate data into summary tables — turning slow GROUP BY queries on millions of rows into fast lookups.
MySQL
Intermediate
4 min read
Rolling Aggregations
Example
CREATE EVENT hourly_sales_summary
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
REPLACE INTO sales_hourly (hour, product_id, total_qty, total_revenue)
SELECT
DATE_FORMAT(NOW() - INTERVAL 1 HOUR, "%Y-%m-%d %H:00:00"),
product_id,
SUM(qty),
SUM(price * qty)
FROM order_items
WHERE created_at BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
GROUP BY product_id;
END; Pro Tip
REPLACE INTO upserts the summary — safe to run on overlapping time windows for idempotency.