Speeding Up MySQL Using Materialized Views

Sometimes, MySQL users would like to allow frequent access to big and active tables. SATS RDBAs already described how to do this in PostgreSQL using mat-views (as discussed in Speeding up PostgreSQL by using Mat-views), and have ported the solution to MySQL. Similar to PostgreSQL, this materialized view logic is missing natively in MySQL, but it could be developed easily with SQL logic.

Though MySQL’s query cache is pretty helpful in many situations, the fact is every entry gets invalidated very fast in an active database, rendering it almost useless. Mat-views in MySQL are a great way of taking full advantage of the query cache as well.

Step 1: Creation of mat-views functions and data dictionary

Create the mat-view table (data dictionary). This will include the original view for each mat-view and the last refresh date and duration as well.

CREATE TABLE matviews (   
mv varchar(64) NOT NULL PRIMARY KEY ,
view varchar(64) NOT NULL ,
last_refresh TIMESTAMP ,
refresh_time INTEGER );

Once this is done, create the create_matview, drop_matview and refresh_matview as per below:

DROP procedure IF EXISTS create_matview;  
CREATE procedure create_matview( matview varchar(64), view_name varchar(64))
NOT DETERMINISTIC
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
SELECT 'ERROR! a matview or table with that name exists. will not proceed' as ERR;
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
SELECT 'ERROR! a matview with that already name exists. will not proceed' as ERR;
IF (select count(*)
from information_schema.tables
where table_schema in (select database())
and table_name=matview
) THEN
call raise_error;
END IF;
SET @time_start = CURRENT_TIMESTAMP();
SET @crtbl= CONCAT('CREATE TABLE ',matview,' as select * from ', view_name);
prepare stmt from @crtbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @time_stop = CURRENT_TIMESTAMP();
SELECT @time_start as Refresh_start, @time_stop as Refresh_end;
INSERT INTO matviews (mv, view, last_refresh,refresh_time) VALUES (matview, view_name, @time_start, UNIX_TIMESTAMP(@time_stop) -UNIX_TIMESTAMP(@time_start));
END//
delimiter ;
delimiter //
DROP procedure IF EXISTS refresh_matview;
CREATE procedure refresh_matview( matview varchar(64))
NOT DETERMINISTIC
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
SELECT 'ERROR! a matview with that name does not exist, will not proceed' as ERR;
IF (select count(*)!=1
from matviews
where mv= matview
) THEN call raise_error;
END IF;
START TRANSACTION;
SET @time_start = CURRENT_TIMESTAMP();
SET @v_name = (SELECT view from matviews where mv=matview limit 1);
SET @crtbl= CONCAT('DELETE FROM ',matview);
prepare stmt from @crtbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @crtbl= CONCAT('INSERT INTO ',matview,' SELECT * FROM ',@v_name);
prepare stmt from @crtbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @time_stop = CURRENT_TIMESTAMP();
SELECT @time_start as Refresh_start, @time_stop as Refresh_end;
update matviews set last_refresh = @time_start, refresh_time=UNIX_TIMESTAMP(@time_stop) -UNIX_TIMESTAMP(@time_start) where mv=matview;
COMMIT;
END//
delimiter ; delimiter //
DROP procedure IF EXISTS drop_matview;
CREATE procedure drop_matview( matview varchar(64))
NOT DETERMINISTIC
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
SELECT 'ERROR! a matview with that name does not exist, will not proceed' as ERR;
SET AUTOCOMMIT=0;
IF (select count(*)!=1 from matviews where mv= matview )
THEN call raise_error;
END IF;
START TRANSACTION;
SET @crtbl= CONCAT('drop table ',matview);
prepare stmt from @crtbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
delete from matviews where mv=matview;
COMMIT;
END//
delimiter ;

There is a simple security mechanism to check for existing tables with that name while creating the mat-view in order (caution — no such mechanism exists on refresh).

Please only use the stored procedures (create_matview, refresh_matview, drop_matview) and don’t edit mat-view table in any way.

Step 2: Create a source view (in this case V_TOP_A_B)

mysql>    CREATE VIEW V_TOP_A_B AS   
-> select V.OBJECT_ID, V.OBJECT_NAME from
-> A join B using (OBJECT_ID)
-> where A.DATE IN (CURRENT_DATE, CURRENT_DATE - INTERVAL 7 DAY)
-> GROUP BY A.OBJECT_ID
-> ORDER BY SUM(A.foo) desc
-> LIMIT 0,250000;
Query OK, 0 rows affected (0.00 sec)

Step 3: Create mat-view

To create mat-view, simply use the create_matview procedure (first parameter is destination MV, second is source view\table).

mysql> call create_matview('MV_TOP_A_B','V_TOP_A_B');  
+--------------------------+--------------------------+
| Refresh_start | Refresh_end |
+--------------------------+--------------------------+
| 2013-11-01 11:40:11 | 2013-11-01 11:40:17 |
+--------------------------+--------------------------+
1 row in set (6.56 sec)
Query OK, 1 row affected (6.56 sec)

Now we can compare the runtime of the source view against the runtime of the mat-view.

mysql> select SQL_NO_CACHE * from V_TOP_A_B limit 5; select SQL_NO_CACHE * from MV_TOP_A_B limit 5;  
+----------------+-------------------+
| OBJECT_ID | OBJECT_NAME |
+----------------+-------------------+
| 20 | foo.com |
| 1011111112 | foo2.com |
| 443 | foo3.com |
| 1331859 | foo4.com |
| 12230 | foo5.com |
+----------------+-------------------+
5 rows in set (4.77 sec)
+----------------+------------------+
| OBJECT_ID | OBJECT_NAME | +----------------+------------------+
| 20 | foo.com |
| 1011111112 | foo2.com |
| 443 | foo3.com |
| 1331859 | foo4.com |
| 12230 | foo5.com | +----------------+------------------+
5 rows in set (0.03 sec)

In this test case, the speed is improved 150x!

Step 4: Additional Usage Notes

Refresh should be done using the refresh_matview stored procedure.

mysql> call refresh_matview('MV_TOP_A_B');  +--------------------------+--------------------------+  
| Refresh_start | Refresh_end | +--------------------------+--------------------------+
| 2013-11-01 11:42:10 | 2013-11-01 11:42:24 | +--------------------------+--------------------------+
1 row in set (14.15 sec)
Query OK, 1 row affected (14.16 sec)

You can add a small cron to do that for you at whatever intervals you would like on Linux — you can schedule this bash script for example:

#!/bin/bash  
mysql -ufoo foobar -e "call refresh_matview('MV_TOP_A_B')"

This is the dictionary (includes last refresh time and duration), feel free to use in order to keep tabs on the mat-views and refresh status.

mysql> select * from mat-views;  +--------------+-----------+---------------------+-------------+  
| mv | view | last_refresh | refresh_time| +--------------+-----------+---------------------+-------------+
| MV_TOP_A_B | V_TOP_A_B | 2013-11-01 11:42:10 | 14 | +--------------+-----------+---------------------+-------------+
1 row in set (0.00 sec)

You can create any indexes you would like on the MV itself to give an additional boost if needed.

Learn more at sats.net, or contact SATS with questions, comments and concerns here.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store