How to Create a Materialized View in MariaDB

How do I create and refresh a materialized view in MariaDB?

A materialized view in MariaDB is an ordinary table that stores the results of a SELECT statement and is refreshed manually or on a schedule.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a materialized view in MariaDB?

MariaDB has no MATERIALIZED VIEW keyword, but you can simulate one by copying query results into a physical table. The table holds pre-computed data, letting dashboards and APIs read quickly.

When should I use one?

Use materialized views for heavy joins or aggregations on Orders and OrderItems that run repeatedly. Pre-computing totals eliminates expensive scans during business hours.

How do I create a materialized view?

Create the target table with CREATE TABLE … AS SELECT. Include only the columns you need and add sensible indexes for look-ups.

Example

CREATE TABLE order_revenue_mv AS SELECT o.id AS order_id, DATE(o.order_date) AS order_day, SUM(oi.quantity * p.price) AS revenue FROM Orders o JOIN OrderItems oi ON oi.order_id = o.id JOIN Products p ON p.id = oi.product_id GROUP BY o.id, order_day; ALTER TABLE order_revenue_mv ADD PRIMARY KEY (order_id), ADD INDEX (order_day);

How do I refresh the data?

Refresh by truncating then reinserting, or use an atomic swap. The Event Scheduler or external cron keeps the view current.

Manual refresh

TRUNCATE TABLE order_revenue_mv; INSERT INTO order_revenue_mv SELECT … -- same SELECT as above

Scheduled refresh

SET GLOBAL event_scheduler = ON; CREATE EVENT refresh_order_revenue_mv ON SCHEDULE EVERY 1 HOUR DO BEGIN TRUNCATE TABLE order_revenue_mv; INSERT INTO order_revenue_mv SELECT … ; END;

Best practices for performance

Add indexes that match your SELECT patterns. Keep each view focused on a single reporting need. Use atomic swaps (RENAME TABLE) to avoid read downtime.

How do I drop a materialized view?

Because it is a table, use DROP TABLE order_revenue_mv; and remove any associated events or triggers.

Why How to Create a Materialized View in MariaDB is important

How to Create a Materialized View in MariaDB Example Usage


-- Daily revenue per customer materialized view
CREATE TABLE customer_daily_revenue_mv AS
SELECT
    c.id               AS customer_id,
    DATE(o.order_date) AS order_day,
    SUM(oi.quantity * p.price) AS daily_total
FROM Customers c
JOIN Orders o      ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id  = o.id
JOIN Products p    ON p.id         = oi.product_id
GROUP BY c.id, order_day;

-- Refresh every night at 02:00
CREATE EVENT refresh_customer_daily_revenue_mv
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 2 HOUR
DO
BEGIN
  TRUNCATE TABLE customer_daily_revenue_mv;
  INSERT INTO customer_daily_revenue_mv
  SELECT ...; -- same SELECT as above
END;

How to Create a Materialized View in MariaDB Syntax


-- Create materialized view
CREATE TABLE <mv_table_name> AS
SELECT column_list
FROM source_tables
[WHERE conditions]
[GROUP BY columns];

-- Optional: improve query speed
ALTER TABLE <mv_table_name>
  ADD [PRIMARY] KEY (column1),
  ADD INDEX (column2);

-- Manual refresh
TRUNCATE TABLE <mv_table_name>;
INSERT INTO <mv_table_name>
SELECT column_list FROM source_tables …;

-- Scheduled refresh (Event Scheduler must be ON)
CREATE EVENT <event_name>
ON SCHEDULE EVERY <interval>
DO
BEGIN
  TRUNCATE TABLE <mv_table_name>;
  INSERT INTO <mv_table_name>
  SELECT column_list FROM source_tables …;
END;

-- Drop materialized view
DROP TABLE <mv_table_name>;

Common Mistakes

Mistake: Querying the base tables instead of the materialized table after creation. This nullifies the performance benefit. Fix: point reports and dashboards to the *_mv table.
Mistake: Forgetting to refresh the view, leading to stale KPIs. Fix: enable the Event Scheduler or schedule an external job to call the refresh script at the required frequency.