How to implement Materialized View with MySQL?

mysql database views materialized-views
GregJohn picture GregJohn · Mar 28, 2010 · Viewed 21.1k times · Source

How to implement Materialized Views?

If not, how can I implement Materialized View with MySQL?

Update:

Would the following work? This doesn't occur in a transaction, is that a problem?

DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;

Answer

Justin Swanhart picture Justin Swanhart · Apr 6, 2011

I maintain a project called Flexviews (http://github.com/greenlion/swanhart-tools) which adds incrementally refreshable materialized views to MySQL (aka fast refresh), even for views that use joins and aggregation. I've been working on this project for three years. It includes a change data capture utility to read the database logs. No triggers are used.

It includes two refresh methods. The first is similar to your method, except a new version is built, and then RENAME TABLE is used to swap the new for the old. At no point is the view unavailable for querying, but 2x the space is used for a short time.

The second method is true "fast refresh", it even has support for aggregation and joins.

There is a blog post about it: http://www.mysqlperformanceblog.com/2011/04/04/flexviews-part-3-improving-query-performance-using-materialized-views/

Flexviews is significantly more advanced than the FromDual example referenced by astander.

Related questions

View's SELECT contains a subquery in the FROM clause

I have two tables and I need to create a view. The tables are: credit_orders(id, client_id, number_of_credits, payment_status) credit_usage(id, client_id, credits_used, date) I use the following query to do this. …

Read More
MySql views performance

If you are going down the road of using views, how can you ensure good performance? Or is it better not to use views in the first place and just incorporate the equivalent into your select statements?

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More