How to Implement Materialized Views in MySQL / MS-SQL

A Materialized View (MV) replaces a SQL multi-table-view (or query) with a new table that holds all data permutations
MV's are used to improve performance, and are preferable to replication where problem is due to an inefficient query plan 

Why views and complex queries can be slow:

• An efficient query execution plan may not be viable
• Joins/View criteria may not allow use of indexes
• Joins/Views may involve calculations or string concatenations which can't be indexed
• Using Joins/Views can force table-scans which have slow performance

Why deploy a MV?   Compared with a normal View, a properly designed/implemented MV can improve performance:

• 2x faster per table involved in the view (due to less I/O)
• up to 10x faster for views that involve calculations
• up to 100x faster for complex joins that force table scans

When to deploy a MV?   An MV logically should be deployed prior to:

• replication for the purpose of improving performance (because it has a much smaller penalty)
• sharding (because it may require application code changes, plus requires separate hard disks to work properly)

What about SQL Server's Indexed Views?   they have severe restrictions, including:

• no views-of-views
• no outer joins
• no COUNT or SUM or MIN/MAX operator use
• no ORDER BY

---

Example: Table and View CREATE's and INSERT's 

CREATE TABLE tState (
   StateID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   StateName CHAR(255) NOT NULL
);
INSERT INTO tState (StateName) VALUES
   ('California'),
   ('Washington');

CREATE TABLE tCity (
   CityID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   CityName CHAR(255) NOT NULL,
   StateIDFK INT NOT NULL
);
INSERT INTO tCity (CityName, StateIDFK) VALUES
   ('Los Angeles', 1),
   ('San Francisco', 1),
   ('Seattle', 2);


CREATE VIEW vStateCity AS
   SELECT StateID, StateName, CityID, CityName
      FROM tState s, tCity c
      WHERE c.StateIDFK = s.StateID;

---

Example: View SELECT 

   SELECT * FROM vStateCity; 

StateName	CityName
California	Los Angeles
California	San Francisco
Washington	Seattle

---

Example: MV Create: 

/* Create the MV table */
CREATE TABLE mvStateCity AS SELECT * FROM vStateCity;

/* Optionally add index(es) for the queries you want to speed up */
CREATE INDEX iStateCity ON mvStateCity(StateName, CityName)

/* Rename the old view to save it and to avoid application re-coding */
/* (For MS-SQL, use syntax 'EXEC sp_rename [old], [new]') */
RENAME TABLE vStateCity TO vStateCityOld;

/* Create the view that points to the MV */
CREATE VIEW vStateCity AS
   SELECT * FROM mvStateCity;

---

Example: MV Query Run Time 

SELECT * FROM vStateCity WHERE STATE= ‘California’ AND CityName LIKE ‘%an%’; 

-----> runs 10x faster than original VStateCity query for large amounts of data

---

Example: The MV Triggers (so MV self-maintains)

(For MS-SQL, omit 'DELIMITER', omit '|', and use syntax 'CREATE TRIGGER [trig] ON [table] AS BEGIN ...')

DELIMITER |

CREATE TRIGGER trig_mviCity AFTER INSERT ON tCity
   FOR EACH ROW BEGIN
      INSERT INTO mvStateCity
         SELECT s.StateID, s.StateName, NEW.CityID, NEW.CityName
         FROM tState s WHERE s.StateID=NEW.StateIDFK;
   END;
|

CREATE TRIGGER trig_mvdCity AFTER DELETE ON tCity
   FOR EACH ROW BEGIN
      DELETE FROM mvStateCity
      WHERE CityID=OLD.CityID;
   END;
|
CREATE TRIGGER trig_mvuCity AFTER UPDATE ON tCity
   FOR EACH ROW BEGIN
      UPDATE mvStateCity SET CityName=NEW.CityName, StateID=NEW.StateIDFK
      WHERE CityID=NEW.CityID;
   END;
|

CREATE TRIGGER trig_mvdState AFTER DELETE ON tState
   FOR EACH ROW BEGIN
      DELETE FROM mvStateCity
      WHERE StateID=OLD.StateID;
   END;
|

DELIMITER ;

---

What’s missing in our MV-create and MV-triggers?

• Generalization (must hand-code and test MV-create and triggers)
• View index (MV primary key) must be unique, which may/may-not be the case
• No error logging within the triggers
• Multiple MV copies possibly on different servers (which can achieve much higher performance)
• Automatic query-dispatching to multiple MV copies
• Automatic sharding