15

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?

  • Very nice and example-rich tutorial on doing materialized views "manually", using only built-in sql features like tables, stored procedures and triggers. Everything from a simple "truncate-insert-into" refresh procedure, over trigger-based immediate updates to logtable based incremental updates and snapshotting functionality: fromdual.com/mysql-materialized-views – user110525 Nov 14 '16 at 19:10
12

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)

ALTERNATIVE #1

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.

ALTERNATIVE #2

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 ...

ALTERNATIVE #3

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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