✏️ Explanatory Question
Level: Advanced — A "gotcha" question, since MySQL does not natively support materialized views.
The key difference is whether the result is stored physically:
| Feature | View | Materialized View |
|---|---|---|
| Stores data? | No (definition only) | Yes (physical result) |
| Data freshness | Always current (live) | Snapshot (needs refresh) |
| Read speed | Slower (runs query) | Very fast (precomputed) |
| Storage cost | Minimal | Full result stored |
| Best for | Real-time data | Expensive aggregations / reports |
| MySQL support | Native | Not native (simulated) |
-- 1) Create a real table to hold the precomputed results
CREATE TABLE mv_dept_summary (
dept_id INT,
dept_name VARCHAR(50),
emp_count INT,
avg_salary DECIMAL(10,2),
refreshed_at DATETIME
);
-- 2) A procedure to (re)populate it
DELIMITER $$
CREATE PROCEDURE RefreshDeptSummary()
BEGIN
TRUNCATE TABLE mv_dept_summary;
INSERT INTO mv_dept_summary
SELECT d.dept_id, d.dept_name, COUNT(e.emp_id),
AVG(e.salary), NOW()
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;
END $$
DELIMITER ;
-- 3) Schedule an automatic refresh with an EVENT (e.g., hourly)
CREATE EVENT ev_refresh_summary
ON SCHEDULE EVERY 1 HOUR
DO CALL RefreshDeptSummary();
-- Now reads are instant (no heavy aggregation each time)
SELECT * FROM mv_dept_summary;