How to implement Materialized View with MySQL?

Question

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 1

I maintain LeapDB (http://www.leapdb.com) 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 13 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.

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

Answer 2

Your example approximates a "full refresh" materialized view. You may need a "fast refresh" view, often used in a data warehouse setting, if the source tables include millions or billions of rows.

You would approximate a fast refresh by instead using insert / update (upsert) joining the existing "view table" against the primary keys of the source views (assuming they can be key preserved) or keeping a date_time of the last update, and using that in the criteria of the refresh SQL to reduce the refresh time.

Also, consider using table renaming, rather than drop/create, so the new view can be built and put in place with nearly no gap of unavailability. Build a new table 'mview_new' first, then rename the 'mview' to 'mview_old' (or drop it), and rename 'mview_new' to 'mview'. In your above sample, your view will be unavailable while your SQL populate is running.

Answer 3

This thread is rather old, so I will try to re-fresh it a bit:

I've been experimenting and even deployed in production several methods for having materialized views in MySQL. Basically all methods assume that you create a normal view and transfer the data to a normal table - the actual materialized view. Then, it's only a question of how you refresh the materialized view.

Here's what I've success with so far:

1. Using triggers - you can set triggers on the source tables on which you build the view. This minimizes the resource usage as the refresh is only done when needed. Also, data in the materialized view is realtime-ish
2. Using cron jobs with stored procedures or SQL scripts - refresh is done on a regular basis. You have more control as to when resources are used. Obviously you data is only as fresh as the refresh-rate allows.
3. Using MySQL scheduled events - similar to 2, but runs inside the database
4. Flexviews - using FlexDC mentioned by Justin. The closest thing to real materialized

I've been collecting and analyzing these methods, their pros and cons in my article Creating MySQL materialized views

looking forwards for feedback or proposals for other methods for creating materialized views in MySQL