How you can implement Materialized Sights?

Otherwise, how do i implement Materialized View with MySQL?


Would the next work? This does not exist in a transaction, is the fact that an issue?

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

I conserve a project known as Flexviews ( which adds incrementally refreshable materialized sights to MySQL (also known as fast refresh), for sights which use joins and aggregation. I have been focusing on this work for 3 years. It offers a big change data capture utility to see the database logs. No triggers are utilized.

It offers two refresh techniques. The very first is much like your method, except a brand new version is made, after which Relabel TABLE can be used to swap the brand new for that old. At no reason may be the view not available for querying, but 2x the area can be used for a short while.

The 2nd technique is true "fast refresh" with support for aggregation (all functions except group_concat and avg(distinct)) and joins.

There's your blog publish about this:

Flexviews is considerably more complex compared to FromDual example recommended by astander.

Your example approximates a "full refresh" materialized view. You might need a "fast refresh" view, frequently utilized in an information warehouse setting, when the source tables include millions or vast amounts of rows.

You'd approximate a quick refresh by rather using place / update (upsert) joining the present "view table" from the primary secrets from the source sights (presuming they may be key maintained) or keeping to start dating ?_duration of the final update, and taking advantage of that within the criteria from the refresh SQL to lessen the refresh time.

Also, think about using table renaming, instead of drop/create, therefore the new view could be built and set up with nearly no gap of unavailability. Develop a new table 'mview_new' first, then relabel the 'mview' to 'mview_old' (or drop it), and relabel 'mview_new' to 'mview'. Inside your above sample, your view is going to be not available while your SQL populate is running.