Materialized Views In MySQL And MariaDB

You can implement materialized views in MySQL. How? Read this post and I will show you 3 different methods for creating a MySQL materialized view.

An application is only as fast as it’s slowest component. If your application uses MySQL your objective is clear:

Improve MySQL query performance to get faster data retrieval and your application will instantly become faster.

In this post I will show you 3 techniques for creating MySQL materialized views and get the best performance for data driven applications.

It is important to understand that MySQL does not natively support materialized views. Unlike some other database systems, you cannot use a CREATE MATERIALIZED VIEW statement. Instead, we have to simulate this functionality by creating a regular table and then implementing a mechanism to keep the data in that table updated. The methods we will explore in this article are using triggers, scheduled events, and third-party tools.

Note for MariaDB Users

Because MariaDB is actually a fork from MySQL, all the techniques presented will work the same for creating MariaDB materialized views and MySQL.  I’ve recently read a good article MariaDB vs MySQL if you are interested in a complete comparison between the two databases. When documenting for this post, I’ve been creating materialized views on MySQL 5.7 but the same techniques work in 2025 on latest versions like MySQL 9.4.1 and MariaDB 11.8 and later versions.

You will have to agree

You create database views for convenience and for performance. When view performance is not enough the next step is creating materialized views. Before turning to materialized views, you should first explore all the performance tweaks you can do to normal MySQL views. For example, you could improve a MySQL view performance simply by creating a well thought MySQL (or MariaDB) indexed view.

MySQL is not the fastest relational database, but

In this post we will take a look at a few options for setting up materialized views in MySQL. We’ll also see what are the pros and cons for these methods.

Both approaches will also work fine for creating a materialized view in MariaDB and in MySQL.

What are materialized views? Materialized View vs View

Having good indexes will give your MySQL view the best performance. But if you work with huge amount of data a simple query might take minutes to execute.

You don’t want the user to wait minutes to load a screen showing data, right?

So what do we do?

Let’s see an use case and how to create and use materialized views in MySQL.

Use case

I was working on a small dashboarding project on Liferay. The dashboard was supposed to display statistics about an application users data.

The application used internally several hundreds MySQL databases (schemas). Each schema had the same tables and structure, but different data corresponding to a different client. If you want to know more about the details of this read my article on handline multi-tenancy in Liferay.

Trust me

Running a query over hundreds of databases every time a user goes to the dashboard was not an option.

The dashboard needed data combined from the same table in 200 different databases. The query was not very complex but it took 22 seconds to execute. Too long for loading only one chart.

Enter the materialized view.

So, basically I needed something like a MySQL view with a cache, such that when queried it gives me the data without executing queries over 200 databases.

CREATE MATERIALIZED VIEW Doesn’t Exist In MySQL

But, there is no MySQL syntax for creating materialized views. You cannot say

CREATE MATERIALIZED VIEW `user_stats` AS 
  SELECT * FROM `DB-1`.USERS WHERE ... 
   UNION 
  SELECT * FROM `DB-2`.USERS WHERE .... 
   UNION ...

There are no materialized views in MySQL, but there are ways to achieve the same results in MySQL.

Let’s see how.

Method 1 – Create a MySQL materialized view – example using triggers

This method is used when you need real-time data in your materialized view.

When any data is changed the trigger will make sure to refresh the materialized view.

Here’s what we do

We will create a MySQL table with the needed structure. This table will be the fake “materialized view”.

To do this simply create the table using a query like:

CREATE TABLE `user_stats` AS 
  SELECT * FROM `DB-1`.USERS WHERE ... 
   UNION 
  SELECT * FROM `DB-2`.USERS WHERE .... 
   UNION ...

This will both create the table and fill it in with data. The data is a snapshot of the data in all the 200 tables from our use case.

How To Refresh The Materialized View

How do we keep the data up-to-date?

That is where triggers come in. If you are not familiar with MySQL triggers, in short they are a mechanism through which database events like inserts, updates and deletes are handled.

This is how to create a MySQL trigger

We need to set triggers on all 3 operations (insert, update and delete) on the source tables in all 200 databases.

The insert trigger should insert a copy of the inserted row in the “materialized view” table.

The update trigger should take the updated row, identify it in the “materialized view” table and do the same update.

The delete trigger should identify and delete the row in the “materialized view”.

Here’s the SQL code:

delimiter |
CREATE TRIGGER MATERIALIZED_VIEW_TRG AFTER INSERT ON USERS
 FOR EACH ROW
 BEGIN
 INSERT INTO USER_STATS(id,login_date, ...) values(new.id,login_date,...);
 END;

delimiter ;

delimiter |
CREATE TRIGGER MATERIALIZED_VIEW_TRG AFTER UPDATE ON USERS
 FOR EACH ROW
 BEGIN
 UPDATE USER_STATS set login_date = new.login_date,... where id = new.id;
 END;
 
delimiter ;

delimiter |
CREATE TRIGGER MATERIALIZED_VIEW_TRG BEFORE DELETE ON USERS
 FOR EACH ROW
 BEGIN
 DELETE FROM USER_STATS where id = old.id;
 END;
 
delimiter ;

There are some articles out there on materialized view using triggers, but as I will show you my approach.

Method 2 – MySQL materialized views using MySQL scheduled events

MySQL events are actions that you can schedule in MySQL. In our case the solution is to schedule the transfer of data between the source and the “materialized view”.

You can schedule events to run every hour, every day, every week etc.

To read more on the syntax for creating a scheduled event please read the MySQL events syntax documentation.

In MySQL scheduled events are not activated by default.

To activate them do this

SET GLOBAL event_scheduler = ON;

or start your MySQL instance with the flag –event-scheduler=ENABLED.

Create the scheduled event

CREATE EVENT `user_stats_daily` 
  ON SCHEDULE EVERY 1 DAY STARTS '2016-06-29 20:00:00' 
  ON COMPLETION NOT PRESERVE ENABLE 
  COMMENT 'Creates a materialized view with user data' DO BEGIN
     	SET GLOBAL group_concat_max_len=1000000;
	select GROUP_CONCAT(CONCAT("SELECT NOW() as last_update,'",shard.name,"' as shardname,
                `name` 'Client name', 
                DATE_FORMAT(IFNULL(`lastLoginDate`,`loginDate`),'%b %d %Y %h:%i %p') 'Last login date', 
                lastLoginIP 'Login IP', 
                active_ 'Active' FROM `DB-", shard.name,
		"_portal`.`organization_`,`DB-",
		shard.name,
		"_portal`.`user_` WHERE `organizationId` in 
                  (select min(organizationId) from `DB-",shard.name,
		"_portal`.`organization_`) 
		") SEPARATOR ' UNION ALL 
		') INTO @stmt_sql
		FROM `portal_db`.`shard` shard;


SET @drop_stmt = "drop table if exists DB-APP._b_user_stats;";

 PREPARE stmt
FROM @drop_stmt;

 EXECUTE stmt;


SET @sql = concat("create table DB-APP._b_user_stats as ",@stmt_sql);

 PREPARE stmt
FROM @sql;

 EXECUTE stmt;

 DEALLOCATE PREPARE stmt;

 END

Let’s analyze what we have here.

First we define the scheduler to run every day at 20:00 (after business hours to avoid slowing the server down).

I used GROUP_CONCAT to dynamically create the SQL query. The query goes in all 200 databases so it is a pretty long string.

That is why we have to do SET GLOBAL group_concat_max_len=1000000; to set the maximum length of the text that GROUP_CONCAT can output.

The ‘shard’ table holds the index of each database. They are numbers 1 through 200, so the SQL will go over all databases from DB-1 to DB-200.

We store the text of the big SQL query in the variable @stmt_sql. Then we delete the “materialized view” to make sure we start fresh, and then create it again using the result of the big SQL query.

You now know another method to create MySQL materialized views.

It’s actually the one I used in production. Having the data daily updated was enough for the dashboard display.

Method 3 – MySQL Flexviews – Real Materialized Views In MySQL (Flexviews is no longer maintained)

While some older guides may mention third-party tools like Flexviews, it’s important to note that this project is no longer actively maintained and is not compatible with modern MySQL versions. Therefore, it is not a recommended solution for new projects. Flexviews has been replaced by LeapDB (see below)

As you probably know, MySQL has been bought by Oracle.

When that happened, the project has split into 2 separate projects: Oracle MySQL and MariaDB

The idea behind MariaDB was to keep the opensource aspect of MySQL going.

One of the latest developments (to date) is something called flexviews.

MySQL Real Materialized Views – MySQL Flexviews

Flexviews are temporary tables that store results of a view. There is an API which offers refresh functions. In this way, you can implement real materialized views in MySQL, not just emulations.

How can I implement flexviews you ask?

Project swanhart-tools which is free to download from Github.

Inside the flexviews folder is the code needed to implement materialized views on MySQL and MariaDB

The requirements for flexviews are:

• MariaDB or MySQL, version 5.1+
• PHP 5.2+ required, 5.3+ is recommended (for FlexCDC)
  • pcntl extension

The tool offers two refresh methods:

• INCREMENTAL which analyzes the binary logs to identitfy the rows that changed and makes the changes in the materialized view table; the advantage is speed, however there are limitations regarding the functions that you can use in the backing SQL query;
• COMPLETE which basically discards the old data in the materialized view table and puts in the new data; the advantage is that the backing SQL query can use any function available but, it will obviously take longer to process

Method 3.1 LeapDB Has Replaced Flexviews Project

Flexviews is no longer actively maintained as an open-source project. It has been ported to LeapDB, which now supports native materialized views with commands like CREATE MATERIALIZED VIEW and related features. The original Flexviews project itself does not receive updates or maintenance anymore.

For teams looking for the power of materialized views without the manual overhead of triggers or schedulers, an alternative approach is to use a modern database system that is compatible with MySQL but offers native support for this feature.

What is LeapDB?

LeapDB is a real-time database designed for high-performance analytics. It is wire-protocol compatible with MySQL, which means you can connect to it using the same client libraries, connectors (like JDBC/ODBC), and BI tools that you already use for MySQL. A key advantage of LeapDB is its built-in, first-class support for materialized views that are always up-to-date. The database automatically and incrementally refreshes the view as new data arrives in the source tables, ensuring queries are both fast and reflect the latest information.

This method provides a “best of both worlds” scenario: you get a simple, declarative syntax for creating views while maintaining compatibility with the MySQL ecosystem.

How to Create a Materialized View in LeapDB

Creating and using a materialized view in LeapDB is a simple, three-step process.

Step 1: Define the Materialized View

You use a standard CREATE MATERIALIZED VIEW statement, similar to PostgreSQL. There is no need to create the underlying table first.

For example, let’s say you have a raw_events table and want to create an hourly summary of user activity.

-- First, assume you have a source table like this:
CREATE TABLE raw_events (
    event_time TIMESTAMP,
    user_id INT,
    event_type VARCHAR(50)
);

-- Now, create the materialized view to aggregate the data:
CREATE MATERIALIZED VIEW hourly_summary AS
SELECT
    DATE_TRUNC('hour', event_time) AS hour,
    event_type,
    COUNT(*) AS event_count
FROM
    raw_events
GROUP BY 1, 2;

Step 2: Let LeapDB Manage the Refresh

Once the view is created, you are done. There are no triggers to configure or schedulers to manage. When new data is inserted, updated, or deleted in the raw_events table, LeapDB automatically updates the hourly_summary view in real-time.

Step 3: Query the View for Fast Results

You can now query the hourly_summary view directly to get aggregated results instantly, without needing to scan the large raw_events table.

-- This query is extremely fast because the results are pre-computed.
SELECT * FROM hourly_summary WHERE event_type = 'login' ORDER BY hour DESC;

Using a system like LeapDB is an excellent option when data freshness is critical and you want to avoid the complexity and potential performance overhead of implementing and maintaining a manual refresh solution in MySQL or MariaDB.

Choosing the Right Method

Choosing the right method for creating and maintaining a materialized view in MySQL or MariaDB depends on your specific requirements.

If you need the data in your materialized view to be as up-to-date as possible, using triggers (Method 1) is the best approach. This method provides near real-time data synchronization, as the materialized view is updated immediately after any change in the source tables. However, this comes at the cost of increased complexity in setup and maintenance, especially if you have many source tables or a complex update logic.

On the other hand, if a certain level of data staleness is acceptable, using a scheduled event (Method 2) is a simpler and more manageable solution. This approach is easier to maintain and can be scheduled to run during off-peak hours to minimize the impact on database performance. The trade-off is that the data will only be as fresh as the last time the event was run.

Third-party tools like Flexviews (Method 3) aimed to provide a more integrated solution, but their use is now limited due to a lack of updates for newer MySQL versions. For modern applications, it is generally better to rely on the trigger or scheduled event methods.

BONUS: Handling JSON Data in Materialized Views

With the increasing use of JSON data types in MySQL and MariaDB, a common and powerful use case for materialized views is to create a summarized, structured report from data stored within JSON columns. Querying JSON data directly can be slow and complex, especially when performing aggregations or filtering on specific keys. By creating a materialized view, you can extract the relevant JSON key-values into regular, indexed columns, making queries significantly faster.

This approach is particularly useful for scenarios like reporting on product attributes, user profiles, or event logs where semi-structured data is stored in a single JSON field.

Example: Creating a Product Sales Summary

Imagine you have an orders table with a JSON column called order_details that stores information about each product sold, including its name, category, and price.

First, let’s create and populate the source table:

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  order_details JSON
);

INSERT INTO orders (order_details) VALUES
('{"product_name": "Laptop", "category": "Electronics", "price": 1200.00}'),
('{"product_name": "Mouse", "category": "Electronics", "price": 25.50}'),
('{"product_name": "Desk Chair", "category": "Furniture", "price": 150.75}'),
('{"product_name": "Keyboard", "category": "Electronics", "price": 75.00}');

Now, we can create a materialized view (a summary table) called electronics_sales_summary to store only the sales data for products in the ‘Electronics’ category. We will extract the product_name and price from the JSON data into their own columns.

Here is the query to create and populate our materialized view:

CREATE TABLE electronics_sales_summary AS
SELECT
  order_id,
  order_date,
  order_details->>'$.product_name' AS product_name,
  CAST(order_details->>'$.price' AS DECIMAL(10, 2)) AS price
FROM
  orders
WHERE
  order_details->>'$.category' = 'Electronics';

Explanation of the Code:

• We use the ->> operator (a shorthand for JSON_UNQUOTE(JSON_EXTRACT(…))) to extract the values for the product_name, price, and category keys from the order_details JSON column.

• We filter the rows to include only those where the category is ‘Electronics’.

• The extracted price is CAST to a DECIMAL type to ensure proper numeric storage and querying.

Now, instead of running a slow JSON query on the orders table every time you need this data, you can simply query the clean and efficient electronics_sales_summary table:

SELECT * FROM electronics_sales_summary;

This materialized view can then be kept up-to-date using either triggers or a scheduled event, as described in the other sections of this article. This technique transforms complex, semi-structured data into a high-performance, query-friendly format.

Now It’s Your Turn

You now have several methods for creating materialized views in MySQL and MariaDB.

You can also combine approaches by simplifying the triggers to only mark the materialized view as out of sync and then the scheduled event to only run in case the view is marked as out of sync.

If you have questions about the implementation of materialized views please drop a comment below.

So, which method are you going to try? Will you try creating materialized views using MySQL triggers?

If you have used other methods for creating materialized views please share. Also, I’m a user of Amazon web services and I know that Amazon deploys it’s own version of MySQL called Aurora. I am pretty sure that the same techniques will work for creating Aurora MySQL materialized view, but I’ve yet to give that a try.