Materialized Views in MySQL

Relational databases like MySQL allows for data to be organized in an atomic and orthogonal manner. What this means is each element of data needs to only be inserted once, but can be related to other pieces of data through queries and views. Views use join and select operations to combine columns of multiple tables into a resulting temporary table.

The problem is that each one of these aggregation (join, group by, etc) operations can get computationally expensive as more rows exist in each table. Thus, a table that represents the results of the view would contain all the information but would not require the expensive operations. These tables are called "materialized views".

Note: Like traditional views, Materialized views are intended to be read-only by client applications.

Because we don't want materialized views to be backed up (they get rather large), it is best practice to create them in adjacent databases.


CREATE DATABASE IF NOT EXISTS `m_my_database`;

In order allow the materialized view to be updated from the primary table, both must be accessible from the same account.


CREATE USER 'my_db_account'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_db_account'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON m_my_database.* TO 'my_db_account'@'%' WITH GRANT OPTION;

Note: For the following, it is assumed that views are already created in `my_database`. In this example, each view is prefixed with `v_.

First, make the table that will hold the materialized views.


CREATE TABLE `m_my_database`.`m_my_joined_table` AS (select * from `my_database`.`v_my_joined_table`);

As elements are inserted and updated in the original tables, the challenge becomes keeping the materialized views up to date. To do this, triggers must be created for insert, update, and delete.


USE `my_database`;
DELIMITER $$
CREATE TRIGGER `my_table_AINS` AFTER INSERT ON `my_table` 
FOR EACH ROW BEGIN
INSERT INTO `m_my_database`.`m_my_joined_table` SELECT * FROM v_my_joined_table WHERE v_my_joined_table.id_col = new.id_col;
END;

USE `my_database`;
DELIMITER $$
CREATE TRIGGER `my_table_AUPD` AFTER UPDATE ON `my_table` FOR EACH ROW BEGIN
DELETE FROM `m_my_database`.`m_my_joined_table` WHERE `m_my_database`.`m_my_joined_table`.id_col = old.id_col;
INSERT INTO `m_my_database`.`m_my_joined_table` SELECT` * FROM v_my_joined_table WHERE v_my_joined_table.id_col = new.id_col;
END

USE `my_database`;
DELIMITER $$
CREATE TRIGGER `my_table_ADEL` AFTER DELETE ON `my_table` FOR EACH ROW BEGIN
DELETE FROM `m_my_database`.`m_my_joined_table` WHERE `m_my_database`.`m_my_joined_table`.id_col = old.id_col;
END;

Now the materialized view `m_my_database`.`m_my_joined_table` can be queried without consequence of aggregation operations.