How to Create a Materialized View in MySQL

A materialized view is a database object that contains the results of a query. It’s similar to a regular view, except that the data is physically stored. Storing the data makes retrieval faster, which helps with complex queries. This guide walks you through how to create one in MySQL.

What is a materialized view?

MySQL doesn’t natively support materialized views, but you can simulate one by combining a regular view with a physical table. You create a table to store the query result and then periodically refresh that table to keep the data up to date.

Create a base table and view

Step 1: Create a base table

Start by creating a base table that will store the materialized data. This table’s structure should match the result of the query you intend to materialize.

CREATE TABLE materialized_view_table (
    -- Define columns here
);

Step 2: Create a view

Next, create a view that defines the query you want to materialize.

CREATE VIEW my_view AS
SELECT
    -- Define your query here
;

Populate the materialized view

Refresh the materialized view

MySQL won’t refresh the table for you, so you need a way to periodically update the base table with the latest data.

TRUNCATE TABLE materialized_view_table;

INSERT INTO materialized_view_table
SELECT * FROM my_view;

You can automate this process using events or triggers.

Query the materialized view

To retrieve data, query the base table.

SELECT * FROM materialized_view_table;

Reading from the base table is faster than querying the view directly.

Managing updates

To keep the materialized view up to date, set up a scheduled job that runs the refresh query at regular intervals. You can do this with MySQL events or external schedulers.

Basedash is built as an AI-native BI platform, so teams can go from ad hoc SQL to trusted answers and dashboards quickly, without the overhead of traditional BI setup.