However, unlike PostgreSQL or Oracle Database, MySQL does not natively support materialized views, which can be a huge disadvantage. That, however, is no reason to stop using MySQL because it is a great database for major applications.
In this tutorial, we will quickly discuss what materialized views are, how they work, instances where they are applicable, and how to implement materialized views in MySQL.
NOTE: I assume you have basic MySQL knowledge before diving into this guide.
What Are Materialized Views?
Materialized views are pre-calculated results of a query stored in a table. Unlike a standard MySQL view, a materialized view is not a window into a database table; it stores actual data.
These object types are super useful when you need immediate access to data, and a regular MySQL query would take time to process. A good example is cases with a large dataset that requires a lot of calculations.
Since materialized views contain real data, they require refreshing to reflect the values updated from the same query. The refresh interval for a materialized view depends on the contents stored and how fast the data is changing.
Does MySQL Support Materialized Views?
The simple answer is NO. In MySQL, there is no single command you can run to get a materialized view. For example, you can’t type the command below and get what you need:
Fortunately, with a few tricks and tools provided natively by MySQL, we can implement a materialized view similar to one natively provided by PostgreSQL, Oracle, and others.
How to Create a Materialized View in MySQL
In this section, I will show you a way to implement a materialized view in MySQL.
NOTE: Although this workshop method illustrates a way to create a materialized view in MySQL, it will not be perfect on all databases.
Suppose we have a schema called (mv) that has a table called info, used to store the sales information as shown in the queries below:
USE mv;
CREATE TABLE info (
id INT PRIMARY KEY AUTO_INCREMENT,
seller_id INT,
_date date,
amount INT
);
INSERT INTO info(seller_id, _date, amount) VALUES (101, "2021-01-05", 200), (111, "2021-01-05", 600), (121, "2021-02-05", 1000);
Using an example table above, we can create a materialized table that stores information of a query such as the one shown below:
The query may not seem effective, considering the size of the database and the information stored in the table above because it will be processed very fast. However, on a large database with a lot of information, a query like the one above might take some time to process.
In such a case, we can create a materialized view to store the information from the above query, allowing us to access the information faster by simply selecting it from the view other than using the raw query every time.
To create a materialized view, we can create a table with the results of the query above:
SELECT seller_id, _date, SUM(amount * 12 * 8) as total_amount FROM info WHERE _date < CURRENT_DATE ORDER BY seller_id
);
How to Refresh A Materialized View
As you now know, a materialized view contains real data; it’s not a snapshot of a table.
That means if the main table is updated, the data in the materialized view table will need refreshing.
To refresh a materialized view, we can use the procedure shown below:
CREATE PROCEDURE refresh_materialv(
OUT dev INT
)
BEGIN
TRUNCATE TABLE materialized_view;
INSERT INTO materialized_view
SELECT seller_id, _date, SUM(amount * 12 * 8) as total_amount
FROM info
WHERE _date SELECT * FROM materialized_view;
+-----------+------------+--------------+
| seller_id | _date | total_amount |
+-----------+------------+--------------+
| 101 | 2021-01-05 | 172800 |
+-----------+------------+--------------+
Next, try updating the data stored in the main table as:
Finally, try updating the materialized_view table data by using the procedure we created above.
SELECT * FROM materialized_view;
This displays the values in the materialized view table updated.
Conclusion
This tutorial discussed what materialized views are, how they work, and how you can implement one when using MySQL. Consider practicing the concepts taught in this tutorial to master working with materialized views.
