Materialized Views in MySQL
Update: Dynamic Materialized Views
Please read this article also. Its an improved versio of the technique described below.
Today i found a workaround for a feature that was realy missing in MySQL.
Let’s say you got a really complicated query that requires a lot of cpu and time to complete, but the resulting data is quite static. That’s the perfect opportunity to use a “Materialized View”. The Bad Thing™ is, mysql does not support what’s usually known as materialized views. But today i found a nice workaround for MySQL 5.
But first let’s clarify what a materialized view is. I found a good explanation here:
“A materialized view is a stored summary containing precomputes results (originating from an SQL select statement).
As the data is precomputed, materialized views allow for (seemingly) faster dataware query answers.”
Okay, so a materialized view saves the result of a query somewhere (e.g. into another table) else.
In my case i had to calculate a rating of users based on votes from other users (Website of http://www.hiphop-battles.com btw) . I’m doing this with some nested views (also a new feature in MySQL5). The whole calculation and query took 0.5 seconds on my athlon xp 3200 machine. Thats way to much to run this query on each page impression.
The trick is a new feature that was introduced in MySQL 5. From the MySQL Manual:
“In MySQL 5.0, you can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:
CREATE TABLE new_tbl SELECT * FROM orig_tbl;“
So its possible to create a new table with structure based on an already existing table. The data of the table gets copied also.
To create a table of a very processing intensive view we run the following query:
CREATE TABLE view_m_demo SELECT * FROM view_demo;
To make it little bit better we put this code into a procedure (also a new feature in MySQL5):
DELIMITER //
CREATE PROCEDURE updateDemoView ()
BEGIN
DROP TABLE IF EXISTS view_m_demo;
CREATE TABLE view_m_demo SELECT * FROM view_demo;
END; //
DELIMITER ;
Now we can call this procedure using a trigger:
delimiter //
CREATE TRIGGER T_updateDemoView AFTER INSERT ON some_involved_table
FOR EACH ROW BEGIN
/* calls the procedure to update out materialized view on each insert */
CALL updateDemoView ();
END //
delimiter ;
The same can be done with an “AFTER UPDATE” trigger. dont use database triggers if the data in the original tables get updated frequently.
Another option in this case is to update the view time triggerd via cron.
To update the materialized view every minute use:
*/1 * * * * echo "CALL updateDemoView ();" | mysql -u user --password=pass database
I hope this helps somebody as much as it helps my projects website loading time ;)
before: 0,48 sec
after: 0,003 sec
