I am using MySQL 5.6. I am not being able to a create materialized view like I can in Oracle. I have seen one or two solutions like Flexview.

Can anybody tell me best way to create a materialized view in MySQL (auto refresh like in Oracle ) with the minimum of complexity?


Materialized Views Do Not Exist in MySQL.

Flexviews has been recommended in the DBA StackExchange before

Since you have it already, some due diligence and elbow grease on your part may be necessary to get going on using it (if you haven't already done so)


You might look into setting up Summary Tables. You are going to need to index your base tables in order to support aggregation before building Summary Tables.


Here is an old post from http://www.materialized.info/ which has a more down-to-earth strategy that looks intriguing. It involves using triggers. Looks like it could be fun ...


If your base tables are not that big and you can live with querying entire tables once a week, try the User Comments section of the MySQL Documentation on CREATE VIEW for ideas. Search for word materialized on that page.

  • In the latest version of MySQL you can create materliazed table dba.stackexchange.com/a/226707/50702 – Revious Mar 19 '19 at 20:14
  • @Revious - That link only shows a case where a transient materialized view is created. But it is then thrown away, hence not "materialized" in the conventional sense. – Rick James Mar 16 at 19:50

I found 2 possible solutions to having materialized views in MySQL:

  1. Create an aggregation table with all the data necessary and then create triggers on the tables where the data comes from.

  2. Create a scheduler that periodically aggregates the data into a table

See more details about how to implement the 2 options in my blog post here: materialized view MySQL


If your underlying data are mostly stable or you can tolerate difference between the view and the data, then consider using the simple-rolap tools, which I wrote for addressing this use case. These allow you to write in separate files complex SQL queries that generate tables. The relationships between the queries are automatically derived and the queries are executed in the appropriate order through the Unix make tool. To refresh the generated tables you'd need to run make clean all. The tool is mostly useful for incrementally building sophisticated queries that execute efficiently based on the pre-calculated results of their inputs.


Not the answer you're looking for? Browse other questions tagged or ask your own question.