Scheduled Report Generation

Scheduled Reports

Pre-compute expensive reports into summary tables so dashboards load instantly instead of running complex aggregations in real time.

Example

DELIMITER //
CREATE EVENT nightly_report
ON SCHEDULE EVERY 1 DAY STARTS "2024-01-01 01:00:00"
DO
BEGIN
  TRUNCATE TABLE daily_report;
  INSERT INTO daily_report (date, new_users, orders, revenue, avg_order)
  SELECT
    CURDATE() - INTERVAL 1 DAY AS date,
    COUNT(DISTINCT u.id),
    COUNT(DISTINCT o.id),
    COALESCE(SUM(o.total), 0),
    COALESCE(AVG(o.total), 0)
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id AND DATE(o.created_at) = CURDATE() - INTERVAL 1 DAY
  WHERE DATE(u.created_at) = CURDATE() - INTERVAL 1 DAY;
END//
DELIMITER ;

SQL

Full Editor

Pro Tip

Pre-computed report tables turn a 5-second dashboard query into a sub-millisecond index lookup.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned