Incremental materialized views

Example 1: Single-table filter (FILTER type)

1. Create a base table with the columnar index enabled.

   CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT) COMMENT 'COLUMNAR=1';
   
   INSERT INTO t1 VALUES (1, 1, 1);

2. Create an incremental materialized view.

   CREATE MATERIALIZED VIEW mv_filter REFRESH FAST ON DEMAND AS
     SELECT * FROM t1 WHERE c2 < 5;

   Perform a full refresh to populate the initial data.

   REFRESH MATERIALIZED VIEW mv_filter;

3. Perform an incremental refresh after the base table data changes.

   -- Change the base table
   INSERT INTO t1 VALUES (2, 2, 3);
   DELETE FROM t1 WHERE c1 = 1;
   
   -- Perform an incremental refresh to process only the changes above
   REFRESH MATERIALIZED VIEW mv_filter;

Example 2: Aggregation statistics (AGGREGATE type)

-- Create a base table with the columnar index enabled
CREATE TABLE orders (
    order_id INT PRIMARY KEY,                  -- Unique order ID (primary key)
    user_id INT NOT NULL,                      -- User ID (GROUP BY column)
    amount DECIMAL(12, 2) NOT NULL,            -- Order amount (aggregation column)
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Order time (standard business field)
)COMMENT 'COLUMNAR=1';

-- Aggregate statistics
CREATE MATERIALIZED VIEW mv_user_order_stats
  REFRESH FAST ON DEMAND
  AS SELECT
       user_id,
       COUNT(*)                   AS order_count,
       SUM(amount * 2)            AS total_amount,
       AVG(CAST(amount AS INT))   AS avg_amount
     FROM orders
     GROUP BY user_id;

Supported aggregate functions: COUNT(*), COUNT(expr), SUM(expr), and AVG(expr).

Example 3: Multi-table INNER JOIN

1. Create the base tables.

   CREATE TABLE t4 (id4 INT PRIMARY KEY, c2 INT, c3 VARCHAR(50)) COMMENT 'COLUMNAR=1';
   CREATE TABLE t5 (id5 INT PRIMARY KEY, c2 INT, c3 VARCHAR(50)) COMMENT 'COLUMNAR=1';
   CREATE TABLE t6 (id6 INT PRIMARY KEY, c2 INT, c3 VARCHAR(50)) COMMENT 'COLUMNAR=1';
   
   INSERT INTO t4 VALUES (1, 10, 'A'), (2, 20, 'B'), (3, 30, 'C');
   INSERT INTO t5 VALUES (1, 100, 'X'), (2, 200, 'Y'), (4, 400, 'Z');
   INSERT INTO t6 VALUES (1, 1000, 'P'), (2, 2000, 'Q'), (3, 3000, 'R');

   Create an incremental materialized view for a multi-table INNER JOIN.

   CREATE MATERIALIZED VIEW mv_inner_with_where_on REFRESH FAST ON DEMAND AS
     SELECT t4.id4, t4.c2, t5.id5, t5.c2 AS t5_c2, t6.id6, t6.c2 AS t6_c2
     FROM t4
     INNER JOIN t5 ON t4.id4 = t5.id5
     INNER JOIN t6 ON t4.id4 = t6.id6
     WHERE t4.c2 > 5 AND t5.c2 > 50;

2. Perform a full refresh and verify.

   -- Perform a full refresh to populate the initial data
   REFRESH MATERIALIZED VIEW mv_inner_with_where_on;
   
   -- Change the base tables
   INSERT INTO t4 VALUES (4, 40, 'D');
   INSERT INTO t6 VALUES (4, 4000, 'S');
   UPDATE t4 SET c2 = 15 WHERE id4 = 1;
   DELETE FROM t5 WHERE id5 = 4;
   INSERT INTO t5 VALUES (5, 400, 'W');
   
   -- Perform an incremental refresh
   REFRESH MATERIALIZED VIEW mv_inner_with_where_on;

Example 4: LEFT JOIN

-- Create base tables with the columnar index enabled
-- Create the products table (joined table / right table)
CREATE TABLE products (
    product_id INT PRIMARY KEY,                -- Join key, must be the primary key
    product_name VARCHAR(100) NOT NULL,        -- Product name
    price DECIMAL(10, 2)                       -- Other business fields
) COMMENT 'COLUMNAR=1';                       

-- Create the order items table (primary table / left table)
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,                   -- Item ID, must be the primary key
    order_id INT NOT NULL,                     -- Order ID
    product_id INT,                            -- Join key, can be NULL (because this is a LEFT JOIN)
    quantity INT NOT NULL,                     -- Purchase quantity
    FOREIGN KEY (product_id) REFERENCES products(product_id) -- Optional foreign key constraint
) COMMENT 'COLUMNAR=1';                       


-- LEFT JOIN
CREATE MATERIALIZED VIEW mv_order_with_product
  REFRESH FAST ON DEMAND
  AS SELECT
       oi.item_id,
       oi.order_id,
       p.product_name,
       oi.quantity
     FROM order_items oi
     LEFT JOIN products p ON oi.product_id = p.product_id;

Example 5: Nested materialized view (computation reuse)

A nested materialized view is built upon another incremental materialized view, allowing you to reuse computation results. For example, you can first filter valid data from a base table, and then perform aggregation on the filtered result. During a refresh, each layer processes only its respective incremental changes.

1. Create a base table.

   CREATE TABLE order_log (
     order_id    INT PRIMARY KEY,
     user_id     INT,
     product_category VARCHAR(50),
     amount      DECIMAL(10,2),
     status      VARCHAR(20),
     created_year INT
   ) COMMENT 'COLUMNAR=1';
   
   INSERT INTO order_log VALUES
     (1, 101, 'electronics', 299.00, 'paid', 2025),
     (2, 102, 'clothing',     59.90, 'paid', 2025),
     (3, 103, 'electronics', 499.00, 'cancelled', 2025),
     (4, 101, 'clothing',    120.00, 'paid', 2025),
     (5, 104, 'electronics',  89.00, 'paid', 2025);

2. Create the first-level incremental materialized view to filter for paid orders.

   CREATE MATERIALIZED VIEW mv_paid_orders REFRESH FAST ON DEMAND AS
     SELECT order_id, user_id, product_category, amount, created_year
     FROM order_log
     WHERE status = 'paid';

   Perform a full refresh to populate the initial data.

   REFRESH MATERIALIZED VIEW mv_paid_orders;

3. Create the second-level incremental materialized view to aggregate statistics by category, based on the first-level view.

   CREATE MATERIALIZED VIEW mv_category_revenue REFRESH FAST ON DEMAND AS
     SELECT product_category,
            COUNT(*)    AS order_count,
            SUM(amount) AS total_revenue
     FROM mv_paid_orders
     GROUP BY product_category;

   Perform a full refresh to populate the initial data.

   REFRESH MATERIALIZED VIEW mv_category_revenue;

4. After the base table data changes, perform an incremental refresh for each level in order.

   -- Add new data to the base table
   INSERT INTO order_log VALUES
     (6, 105, 'electronics', 199.00, 'paid', 2025),
     (7, 106, 'clothing',     75.00, 'cancelled', 2025);
   
   -- Refresh in dependency order: first level, then second level
   REFRESH MATERIALIZED VIEW mv_paid_orders;
   REFRESH MATERIALIZED VIEW mv_category_revenue;
   
   -- Verify the result: order_id=7 is filtered out by the first level because its status is 'cancelled'
   -- and will not be included in the second-level statistics.
   SELECT * FROM mv_category_revenue;

List of materialized views

SELECT
  table_name,
  table_schema,
  base_tables,
  first_refresh_time,
  refresh_strategy,
  last_start_time,
  last_end_time
FROM mysql.view_materialized_info
WHERE refresh_strategy = 'FAST';

All materialized views

SELECT * FROM mysql.view_materialized_info;

Refresh task queue

SELECT * FROM information_schema.materialized_view_refresh_queue;

How to verify incremental refresh?

Use the following statement to verify that the refresh_strategy is FAST. You can also check the last_refresh_status column for the result of the most recent refresh.

SELECT table_name, refresh_strategy, last_start_time, last_end_time
FROM mysql.view_materialized_info;

When is the materialized view updated?

Before a refresh, the view's data reflects its state at the time of the last refresh. To update the data, you must manually run REFRESH MATERIALIZED VIEW mv_name.

Is the base table locked during refresh?

No. You can continue to perform normal read and write operations on the base table during an incremental refresh without any impact.

Is aggregation supported for incremental refresh?

Incremental refresh is currently supported for single-table aggregation. Multi-table aggregation and UNION ALL are not currently supported.

What is the minimum refresh interval?

The minimum refresh interval is currently 1 second.