Materialized Views with MySQL (2023)

Content

• What is a Materialized View?
• Implement your own Materialized Views
• Refreshing materialized views
• Hands on
• Create your own Materialized View:
• Refresh Materialized View on demand
• Refresh Materialized View immediate
• Materialized Views with snapshotting functionality
• Some performance benchmarks for our Materialized Views:
• Outlook
• Conclusion
• Literature

A Materialized View (MV) is the pre-calculated (materialized) result of a query. Unlike a simple VIEW the result of a Materialized View is stored somewhere, generally in a table. Materialized Views are used when immediate response is needed and the query where the Materialized View bases on would take to long to produce a result.Materialized Views have to be refreshed once in a while. It depends on the requirements how often a Materialized View is refreshed and how actual its content is.Basically a Materialized View can be refreshed immediately or deferred, it can be refreshed fully or to a certain point in time.MySQL does not provide Materialized Views by itself. But it is easy to build Materialized Views yourself.

A short example for how this could be done is the following query:

SELECT COUNT(*) FROM MyISAM_table;

returns immediate result because the counter is stored in the table header. The following query can take some seconds up to minutes:

SELECT COUNT(*) FROM innodb_huge;

A possible solution for this would be to create a table where all InnoDB row counts are stored in

CREATE TABLE innodb_row_count ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , schema_name VARCHAR(64) NOT NULL , table_name VARCHAR(64) NOT NULL , row_count INT UNSIGNED NOT NULL);

Depending on the needed correctness of this information the table can be refreshed once a day (least used resources on the system but biggest error in result), once an hour or in most extreme case after every change (slowest)!

An other possibility would be to get the data from the information schema. But this information can be up to 20% wrong!

SELECT table_schema, table_name, table_rows FROM information_schema.tables WHERE table_type = 'BASE TABLE';

Materialized Views can be refreshed in different kinds. They can be refreshed:

• never (only once in the beginning, for static data only)
• on demand (for example once a day, for example after nightly load)
• immediately (after each statement)

A refresh can be done in the following ways:

• completely (slow, full from scratch)
• deferred (fast, by a log table)

By storing the change information in a log table. Also some snapshots or time delayed states can be produced:

• refresh up to date
• refresh full

To understand all this more in detail it is probably easiest to do some examples. Assume we have a table sales:

CREATE TABLE sales ( sales_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , product_name VARCHAR(128) NOT NULL , product_price DECIMAL(8,2) NOT NULL , product_amount SMALLINT NOT NULL);INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1), (NULL, 'Apple', 2.40, 2), (NULL, 'Apple', 4.05, 3), (NULL, 'Pear', 6.30, 2), (NULL, 'Pear', 12.20, 4), (NULL, 'Plum', 4.85, 3);SELECT * FROM sales;

And now we want to know the price sold and the money earned per product:

EXPLAINSELECT product_name , SUM(product_price) AS price_sum, SUM(product_amount) AS amount_sum , AVG(product_price) AS price_avg, AVG(product_amount) amount_agg , COUNT(*) FROM sales GROUP BY product_name ORDER BY price_sum;+-------------+-------+------+---------------+------+---------------------------------+| select_type | table | type | possible_keys | rows | Extra |+-------------+-------+------+---------------+------+---------------------------------+| SIMPLE | sales | ALL | NULL | 6 | Using temporary; Using filesort |+-------------+-------+------+---------------+------+---------------------------------+

On such a small table it is pretty fast but when you have hundreds of products and millions of sales transactions it can take minutes to hours!

DROP TABLE sales_mv;CREATE TABLE sales_mv ( product_name VARCHAR(128) NOT NULL , price_sum DECIMAL(10,2) NOT NULL , amount_sum INT NOT NULL , price_avg FLOAT NOT NULL , amount_avg FLOAT NOT NULL , sales_cnt INT NOT NULL , UNIQUE INDEX product (product_name));INSERT INTO sales_mvSELECT product_name , SUM(product_price), SUM(product_amount) , AVG(product_price), AVG(product_amount) , COUNT(*) FROM salesGROUP BY product_name;

This is up to now the easiest part! And, as expected we get the correct result:

mysql> SELECT * FROM sales_mv;+--------------+-----------+------------+-----------+------------+-----------+| product_name | price_sum | amount_sum | price_avg | amount_avg | sales_cnt |+--------------+-----------+------------+-----------+------------+-----------+| Apple | 7.70 | 6 | 2.56667 | 2 | 3 || Pear | 18.50 | 6 | 9.25 | 3 | 2 || Plum | 4.85 | 3 | 4.85 | 3 | 1 |+--------------+-----------+------------+-----------+------------+-----------+3 rows in set (0.00 sec)

This would cover the refreshment mode "NEVER" But this is not what we generally want to do.

Refreshing the Materialized View on demand can be implemented with a Stored Procedure as follows:

DROP PROCEDURE refresh_mv_now;DELIMITER $$CREATE PROCEDURE refresh_mv_now ( OUT rc INT)BEGIN TRUNCATE TABLE sales_mv; INSERT INTO sales_mv SELECT product_name , SUM(product_price), SUM(product_amount) , AVG(product_price), AVG(product_amount) , COUNT(*) FROM sales GROUP BY product_name; SET rc = 0;END;$$DELIMITER ;

To check if it works the following statement were used:

CALL refresh_mv_now(@rc);SELECT * FROM sales_mv;+--------------+-----------+------------+-----------+------------+-----------+| product_name | price_sum | amount_sum | price_avg | amount_avg | sales_cnt |+--------------+-----------+------------+-----------+------------+-----------+| Apple | 7.70 | 6 | 2.56667 | 2 | 3 || Pear | 18.50 | 6 | 9.25 | 3 | 2 || Plum | 4.85 | 3 | 4.85 | 3 | 1 |+--------------+-----------+------------+-----------+------------+-----------+INSERT INTO sales VALUES (NULL, 'Apple', 2.25, 3), (NULL, 'Plum', 3.35, 1), (NULL, 'Pear', 1.80, 2);CALL refresh_mv_now(@rc);SELECT * FROM sales_mv;+--------------+-----------+------------+-----------+------------+-----------+| product_name | price_sum | amount_sum | price_avg | amount_avg | sales_cnt |+--------------+-----------+------------+-----------+------------+-----------+| Apple | 9.95 | 9 | 2.4875 | 2.25 | 4 || Pear | 20.30 | 8 | 6.76667 | 2.66667 | 3 || Plum | 8.20 | 4 | 4.1 | 2 | 2 |+--------------+-----------+------------+-----------+------------+-----------+

To make the output a little nicer we can add a VIEW on the Materialized View table as follows:

CREATE VIEW sales_v ASSELECT product_name, price_sum, amount_sum, price_avg, amount_avg FROM sales_mv;

To do a full refresh after each statement does not make sense. But we still would like to have to proper result. To do this it is a little bit more complicated.

On every INSERT on the sales table we have to update our Materialized View. We can implement this transparently by INSERT/UPDATE/DELETE triggers on the sales table:

Now let us create the needed triggers:

DELIMITER $$CREATE TRIGGER sales_insAFTER INSERT ON salesFOR EACH ROWBEGIN SET @old_price_sum = 0; SET @old_amount_sum = 0; SET @old_price_avg = 0; SET @old_amount_avg = 0; SET @old_sales_cnt = 0; SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0) , IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0) FROM sales_mv WHERE product_name = NEW.product_name INTO @old_price_sum, @old_amount_sum, @old_price_avg , @old_amount_avg, @old_sales_cnt ; SET @new_price_sum = @old_price_sum + NEW.product_price; SET @new_amount_sum = @old_amount_sum + NEW.product_amount; SET @new_sales_cnt = @old_sales_cnt + 1; SET @new_price_avg = @new_price_sum / @new_sales_cnt; SET @new_amount_avg = @new_amount_sum / @new_sales_cnt; REPLACE INTO sales_mv VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg , @new_amount_avg, @new_sales_cnt) ;END;$$DELIMITER ;

DELIMITER $$CREATE TRIGGER sales_delAFTER DELETE ON salesFOR EACH ROWBEGIN SET @old_price_sum = 0; SET @old_amount_sum = 0; SET @old_price_avg = 0; SET @old_amount_avg = 0; SET @old_sales_cnt = 0; SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0) , IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0) FROM sales_mv WHERE product_name = OLD.product_name INTO @old_price_sum, @old_amount_sum, @old_price_avg , @old_amount_avg, @old_sales_cnt ; SET @new_price_sum = @old_price_sum - OLD.product_price; SET @new_amount_sum = @old_amount_sum - OLD.product_amount; SET @new_price_avg = @new_price_sum / @new_amount_sum; SET @new_sales_cnt = @old_sales_cnt - 1; SET @new_amount_avg = @new_amount_sum / @new_sales_cnt; REPLACE INTO sales_mv VALUES(OLD.product_name, @new_price_sum, @new_amount_sum , IFNULL(@new_price_avg, 0), IFNULL(@new_amount_avg, 0) , @new_sales_cnt) ;END;$$DELIMITER ;

DELIMITER $$CREATE TRIGGER sales_updAFTER UPDATE ON salesFOR EACH ROWBEGIN SET @old_price_sum = 0; SET @old_amount_sum = 0; SET @old_price_avg = 0; SET @old_amount_avg = 0; SET @old_sales_cnt = 0; SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0) , IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0) FROM sales_mv WHERE product_name = OLD.product_name INTO @old_price_sum, @old_amount_sum, @old_price_avg , @old_amount_avg, @old_sales_cnt ; SET @new_price_sum = @old_price_sum + (NEW.product_price - OLD.product_price); SET @new_amount_sum = @old_amount_sum + (NEW.product_amount - OLD.product_amount); SET @new_sales_cnt = @old_sales_cnt; SET @new_price_avg = @new_price_sum / @new_sales_count; SET @new_amount_avg = @new_amount_sum / @new_sales_cnt; REPLACE INTO sales_mv VALUES(OLD.product_name, @new_price_sum, @new_amount_sum , IFNULL(@new_price_avg, 0), IFNULL(@new_amount_avg, 0) , @new_sales_cnt) ;END;$$DELIMITER ;

And now let us see what the result is:

INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1);INSERT INTO sales VALUES (NULL, 'Apple', 2.40, 2);INSERT INTO sales VALUES (NULL, 'Apple', 4.05, 3);INSERT INTO sales VALUES (NULL, 'Pear', 6.30, 2);INSERT INTO sales VALUES (NULL, 'Pear', 12.20, 4);INSERT INTO sales VALUES (NULL, 'Plum', 4.85, 3);DELETE FROM sales WHERE sales_id = 5;DELETE FROM sales WHERE sales_id = 4;UPDATE sales SET product_amount = 3 where sales_id = 2;SELECT * from sales_v;

The difference to the example above is, that the changes will not be applied immediately. The changes will be stored in a log table and the Materialized View is refreshed after a certain time period for a certain amount of time.

In addition to the example above we need an additional log table:

CREATE TABLE sales_mvl ( product_name VARCHAR(128) NOT NULL , product_price DECIMAL(8,2) NOT NULL , product_amount SMALLINT NOT NULL , sales_id INT UNSIGNED NOT NULL , product_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP());

Instead of updating the mv the log is filled:

DROP TRIGGER sales_ins;DELIMITER $$CREATE TRIGGER sales_insAFTER INSERT ON salesFOR EACH ROWBEGIN INSERT INTO sales_mvl VALUES (NEW.product_name, NEW.product_price, NEW.product_amount , NEW.sales_id, NULL);END;$$DELIMITER ;

DROP TRIGGER sales_del;DELIMITER $$CREATE TRIGGER sales_delAFTER DELETE ON salesFOR EACH ROWBEGIN DELETE FROM sales_mvl WHERE sales_id = OLD.sales_id;END;$$DELIMITER ;

DROP TRIGGER sales_upd;DELIMITER $$CREATE TRIGGER sales_updAFTER UPDATE ON salesFOR EACH ROWBEGIN UPDATE sales_mvl SET product_name = NEW.product_name , product_price = NEW.product_price , product_amount = NEW.product_amount , sales_id = NEW.sales_id , product_ts = CURRENT_TIMESTAMP() WHERE sales_id = OLD.sales_id;END;$$DELIMITER ;

And a Stored Procedure for refreshing the Materialized View is built. Possible modes are:

• REFRESH FULL (up to now)
• REFRESH (up to a certain time stamp)
• REBUILD (fully rebuild and clean MV log table)

DELIMITER $$CREATE PROCEDURE refresh_mv ( IN method VARCHAR(16) , IN ts TIMESTAMP , OUT rc INT)BEGINIF UPPER(method) = 'REBUILD' THEN TRUNCATE TABLE sales_mvl; TRUNCATE TABLE sales_mv; INSERT INTO sales_mv SELECT product_name , SUM(product_price), SUM(product_amount), AVG(product_price) , AVG(product_amount), COUNT(*) FROM sales GROUP BY product_name ;ELSEIF UPPER(method) = 'REFRESH FULL' THEN REPLACE INTO sales_mv SELECT product_name, SUM(price_sum), SUM(amount_sum) , SUM(price_sum)/SUM(sales_cnt), SUM(amount_sum)/SUM(sales_cnt) , SUM(sales_cnt) FROM ( SELECT product_name, price_sum, amount_sum, sales_cnt FROM sales_mv UNION ALL SELECT product_name , SUM(product_price), SUM(product_amount), COUNT(*) FROM sales_mvl GROUP BY product_name ) x GROUP BY product_name ; TRUNCATE TABLE sales_mvl; SET rc = 0;ELSEIF UPPER(method) = 'REFRESH' THEN REPLACE INTO sales_mv SELECT product_name, SUM(price_sum), SUM(amount_sum) , SUM(price_sum)/SUM(sales_cnt), SUM(amount_sum)/SUM(sales_cnt) , SUM(sales_cnt) FROM ( SELECT product_name, price_sum, amount_sum, sales_cnt FROM sales_mv UNION ALL SELECT product_name , SUM(product_price), SUM(product_amount), COUNT(*) FROM sales_mvl WHERE product_ts < ts GROUP BY product_name ) x GROUP BY product_name ; DELETE FROM sales_mvl WHERE product_ts < ts ; SET rc = 0;ELSE SET rc = 1;END IF;END;$$DELIMITER ;

And now let us test if it works correctly...

INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1);wait some timeINSERT INTO sales VALUES (NULL, 'Apple', 2.40, 2);wait some timeINSERT INTO sales VALUES (NULL, 'Apple', 4.05, 3);wait some timeINSERT INTO sales VALUES (NULL, 'Pear', 6.30, 2);wait some timeINSERT INTO sales VALUES (NULL, 'Pear', 12.20, 4);wait some timeINSERT INTO sales VALUES (NULL, 'Plum', 4.85, 3);SELECT * from sales_mvl;SELECT * from sales_v;CALL refresh_mv('REFRESH', '2006-11-06 10:57:55', @rc);SELECT * from sales_v;CALL refresh_mv('REFRESH FULL', NULL, @rc);SELECT * from sales_v;CALL refresh_mv('REBUILD', NULL, @rc);SELECT * from sales_v;

For the performance benchmark a set of 100 k sales rows was created. This set of rows should simulate a continuous INSERT flow into our database. The whole test was done when all files and all database table were cache in memory. To avoid side effects during measurement no other activities should happen on this machine.

Load into table sales without any triggers as baseline:

Load into table sales with some Materialized View functionality:

• If you have a continuous data flow and/or concurrent transactions it may be better to use InnoDB instead of MyISAM tables.
• Locking the table in the Triggers/Stored Procedure may prevent wrong data this has to be tested.
• Delayed INSERT may help to speed up the load process.
• Eventually it makes sense to build/refresh the Materialized Vied in parallel?

• Triggers in MySQL (5.0.27) are not terribly fast.
• Materialized Views can help to speed up queries which rely heavily on some aggregated results.
• If INSERT speed is not a matter this feature can help to decrease read load on the system.
• It is a little tricky to implement.
• MV come only in play if huge amount of data are used and database are not memory based anymore.

[ 1 ]

LOAD DATAINFILE '/tmp/sales_outfile_100k.dmp'INTO TABLE sales;

[ 2 ]

time mysql -u root test << EOF;TRUNCATE TABLE sales;SOURCE /tmp/sales_multirowinsert_100k.sqlEOF

[ 3 ]

time mysql -u root test << EOF;TRUNCATE TABLE sales;SOURCE tmp/sales_extended_100k.sqlEOF

[ 4 ]

time mysql -u root test << EOF;TRUNCATE TABLE sales;SOURCE /tmp/sales_extended_nolock_100k.sqlEOF

[ 5 ]

INSERT INTO sales_mvSELECT product_name , SUM(product_price), SUM(product_amount) , AVG(product_price), AVG(product_amount) , COUNT(*) FROM salesGROUP BY product_name;

[ 6 ]

SELECT * FROM sales_mv;

[ 7 ] Wikipedia: Materialized Views