MySQL and Materialized Views
I was poking around the MySQL Worklog again over the weekend, and found a request for materialized views for MySQL. This feature has existed in Oracle for a while, in DB2 as a materialized query table, and appeared in MS SQL Server 2000 and 2005 as indexed views.
What is a materialized view?
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. (from Oracle).
Essentially a materialized view lets you tell the database to periodically refresh a table with the results of a query. You may join, group, and perform calculations. The goal is to increase query performance in a read-heavy environment.
Additionally, at least in the other DBs listed, you can put indexes on the fields within the view.
Disadvantages:
-
There can be issues with altering underlying tables, just as if you were to remove a column that a standard view references.
-
If you’re inserting and updating into the base tables frequently, you will either see a performance hit or have to deal with stale data.
-
Since the data is actually stored as a table on disk, it can take up considerable space
According to the high level architecture
Support creation of materialized views, with only the bare minimum – no automatic refresh, no query rewrite.
Which would be a shame, because it seems that adding in automatic refresh would be a pretty small part of a very complex feature. I haven’t dealt with any of the MySQL codebase so this is just my speculation.
Of course, when looking at any feature, it’s important to determine how useful it actually would be to implement. What is the target audience, and are they the existing customers? What’s the goal of adding this feature?
For ther reading, Database journal has a very good overview of indexed views in SQL Server.
If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.