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.
I found 2 possible solutions to having materialized views in MySQL:
Create an aggregation table with all the data necessary and then create triggers on the tables where the data comes from.
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.
What was once Flexviews is now LeapDB and you can find it at http://www.leapdb.com. Materialized views can be created with the CREATE [INCREMENTAL] MATERIALIZED VIEW command and refreshed using the leapdb.refresh() stored procedure. Change data capture is supported by a java utility based on Debezium. In addition to Materialized Views, LeapDB also supports columnar storage and automatic bitmap indexing via the WARP storage engine. LeapDB is based on MySQL 8.
