MySQL Materialized Views: How to Emulate Them (and When to Stop)

Does MySQL Support Materialized Views?

No. MySQL Community and Enterprise Server have no native materialized views in 8.0, 8.4 LTS or 9.x. A MySQL VIEW stores only its query, not its result. Since MySQL 9.5, CREATE MATERIALIZED VIEW exists only on MySQL HeatWave, Oracle’s managed service. On a self-managed server you emulate one with a summary table that you refresh yourself.

A MySQL view is a stored SELECT. The CREATE VIEW reference is explicit that selecting from a view runs its defining query. MySQL executes the view either by merging its definition into your query (ALGORITHM = MERGE) or by building a temporary table for the duration of the statement (ALGORITHM = TEMPTABLE). Neither keeps a result between queries, and the view restrictions page states it directly: “It is not possible to create an index on a view.”

The HeatWave exception. The MySQL 9.x CREATE VIEW syntax now includes an optional MATERIALIZED keyword, but the 9.6 reference manual says “Materialized views is only supported on MySQL HeatWave.” Per the HeatWave user guide, materialized views arrived in 9.5, 9.6 added automatic query substitution, and a materialized view can reference base tables only, not other materialized views. If you run MySQL yourself, on RDS or Aurora, or on any other managed service, none of that applies.

MariaDB doesn’t have them either. MariaDB has no CREATE MATERIALIZED VIEW; the feature request (MDEV-12163) has been open since 2017. The patterns below work on MariaDB too.

What people actually want. When someone asks for a MySQL materialized view, they usually want three things a view cannot give them: the result of an expensive aggregation or join stored on disk, indexes on that result, and a refresh process that keeps it acceptably current. If you are coming from Postgres, that is what CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW do out of the box. In MySQL you build all three yourself.

Every example below uses the same source table and target result: per-customer daily order totals.

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  status      VARCHAR(20)     NOT NULL,
  amount      DECIMAL(12,2)   NOT NULL,
  created_at  DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at  DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                              ON UPDATE CURRENT_TIMESTAMP(6),
  KEY idx_customer_created (customer_id, created_at)
) ENGINE = InnoDB;

-- The "materialized view": a plain table with a real primary key and indexes.
CREATE TABLE daily_customer_totals (
  order_day    DATE            NOT NULL,
  customer_id  BIGINT UNSIGNED NOT NULL,
  order_count  INT UNSIGNED    NOT NULL,
  total_amount DECIMAL(14,2)   NOT NULL,
  PRIMARY KEY (order_day, customer_id),
  KEY idx_customer (customer_id, order_day)
) ENGINE = InnoDB;

Pattern 1: Summary Table with a Scheduled Rebuild

The closest equivalent to Postgres’s REFRESH MATERIALIZED VIEW is to rebuild the whole summary table on a schedule, then swap it in so readers never see a half-built result.

Why RENAME TABLE. The RENAME TABLE reference guarantees that a multi-table rename “is done atomically; no other session can access any of the tables while the rename is in progress,” and that if any part fails, “no changes are made.” Readers see the old table right up until the swap, then the new one. No reader ever sees an empty or partial table.

Why the Event Scheduler. MySQL’s built-in scheduler runs SQL on an interval without cron or an external job runner. It is on by default in 8.4; check with SHOW VARIABLES LIKE ’event_scheduler’; and enable it with SET GLOBAL event_scheduler = ON; if a managed service turned it off.

DELIMITER $$

CREATE EVENT refresh_daily_customer_totals
  ON SCHEDULE EVERY 15 MINUTE
  STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
  ON COMPLETION PRESERVE
  COMMENT 'Full rebuild of daily_customer_totals with atomic swap'
  DO
  BEGIN
    -- Skip this run if the previous one is still going.
    IF GET_LOCK('refresh_daily_customer_totals', 0) = 1 THEN

      -- Read orders as a consistent snapshot without share-locking it.
      SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

      DROP TABLE IF EXISTS daily_customer_totals_new, daily_customer_totals_old;
      CREATE TABLE daily_customer_totals_new LIKE daily_customer_totals;

      INSERT INTO daily_customer_totals_new
        (order_day, customer_id, order_count, total_amount)
      SELECT DATE(created_at), customer_id, COUNT(*), SUM(amount)
      FROM orders
      GROUP BY DATE(created_at), customer_id;

      RENAME TABLE daily_customer_totals     TO daily_customer_totals_old,
                   daily_customer_totals_new TO daily_customer_totals;

      DROP TABLE daily_customer_totals_old;

      DO RELEASE_LOCK('refresh_daily_customer_totals');
    END IF;
  END$$

DELIMITER ;

Three details in that event matter more than they look.

The GET_LOCK guard. The CREATE EVENT reference warns that “if a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously,” and suggests GET_LOCK() as the fix. Without it, a slow rebuild on a busy day stacks up concurrent rebuilds that fight over the same _new table.

READ COMMITTED for the rebuild. Under the default REPEATABLE READ, INSERT INTO t SELECT ... FROM s takes shared next-key locks on every row it reads from the source. Under READ COMMITTED, InnoDB reads the source as a consistent read with no locks (InnoDB locking reference). On a table that takes writes all day, that is the difference between a rebuild that blocks checkout and one that doesn’t.

Replicas. Events replicated from a source are marked DISABLE ON REPLICA and do not run there. Your summary table reaches replicas as ordinary replicated writes, which is usually what you want. After a failover, re-enable the event on the new source with ALTER EVENT ... ENABLE.

Where it breaks. The result is as stale as your interval plus the rebuild time, and the rebuild cost grows with the whole table, not with how much changed. At a few million rows a 15-minute full rebuild is cheap. At a few billion, it becomes a batch job that competes with production traffic, and you start shrinking the scope (“only rebuild the last 7 days”) by hand. That pressure is what leads teams to incremental refresh.

Pattern 2: Trigger-Maintained Summary Tables

If the summary has to reflect each write immediately, you can maintain it inside the write itself with triggers. Each insert, update or delete on orders adjusts the matching row in daily_customer_totals in the same transaction.

Start with a one-time backfill, then create the triggers. Do both during a quiet period or with writes paused, or rows written between the backfill and the trigger creation will be missed (see the reconciliation note below).

INSERT INTO daily_customer_totals (order_day, customer_id, order_count, total_amount)
SELECT DATE(created_at), customer_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY DATE(created_at), customer_id;

DELIMITER $$

CREATE TRIGGER orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO daily_customer_totals (order_day, customer_id, order_count, total_amount)
  VALUES (DATE(NEW.created_at), NEW.customer_id, 1, NEW.amount)
  ON DUPLICATE KEY UPDATE
    order_count  = order_count + 1,
    total_amount = total_amount + NEW.amount;
END$$

CREATE TRIGGER orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NOT (OLD.customer_id <=> NEW.customer_id
          AND DATE(OLD.created_at) <=> DATE(NEW.created_at)
          AND OLD.amount <=> NEW.amount) THEN

    -- Remove the old row's contribution...
    UPDATE daily_customer_totals
       SET order_count  = order_count - 1,
           total_amount = total_amount - OLD.amount
     WHERE order_day   = DATE(OLD.created_at)
       AND customer_id = OLD.customer_id;

    DELETE FROM daily_customer_totals
     WHERE order_day   = DATE(OLD.created_at)
       AND customer_id = OLD.customer_id
       AND order_count = 0;

    -- ...then add the new row's contribution.
    INSERT INTO daily_customer_totals (order_day, customer_id, order_count, total_amount)
    VALUES (DATE(NEW.created_at), NEW.customer_id, 1, NEW.amount)
    ON DUPLICATE KEY UPDATE
      order_count  = order_count + 1,
      total_amount = total_amount + NEW.amount;
  END IF;
END$$

CREATE TRIGGER orders_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  UPDATE daily_customer_totals
     SET order_count  = order_count - 1,
         total_amount = total_amount - OLD.amount
   WHERE order_day   = DATE(OLD.created_at)
     AND customer_id = OLD.customer_id;

  DELETE FROM daily_customer_totals
   WHERE order_day   = DATE(OLD.created_at)
     AND customer_id = OLD.customer_id
     AND order_count = 0;
END$$

DELIMITER ;

Because the triggers run inside the writing transaction, a reader never sees an order without its contribution to the total. That is the appeal. The costs are real, though, and they compound as write volume grows.

Write amplification. Every INSERT into orders now also writes to daily_customer_totals: an extra index lookup, an extra row lock, extra redo log and extra binlog. An update that moves an order between groups touches up to three summary rows. Add a second summary table and every write pays twice.

Hot-row lock contention. All orders for the same customer on the same day update the same summary row, so their transactions serialize on that row’s lock. Per-customer rollups spread the load. A global rollup (daily revenue across all customers) funnels every order through one row, and throughput on orders drops to whatever one row lock allows. Deadlocks between the summary update and other statements in the same transaction also become routine.

Updates, deletes and non-additive aggregates. COUNT and SUM can be decremented. MIN, MAX, COUNT(DISTINCT ...) and medians cannot: when the current maximum is deleted, the trigger has no way to know the new one without rescanning the group. Status filters add another branch to every trigger (“did this update move the order in or out of the counted set?”), and each branch is a place for the summary to drift.

Silent misses. The trigger restrictions spell out several: “Triggers are not activated by foreign key actions,” so a cascading delete from customers leaves stale totals behind. TRUNCATE TABLE does not fire delete triggers. With row-based replication, “triggers on the replica are not activated by statements originating on the source,” which is fine for this design (the summary rows replicate as row events) but surprises people who put triggers only on a replica.

Reconciliation. Because of all of the above, production trigger-maintained summaries almost always ship with a nightly job that recomputes the totals from orders and compares or overwrites. At that point you are running Pattern 1 as well, to keep Pattern 2 honest.

A note on syntax: the VALUES() function inside ON DUPLICATE KEY UPDATE is deprecated in favor of row aliases (VALUES (...) AS new ON DUPLICATE KEY UPDATE c = new.c). The triggers above use NEW.amount directly, which avoids both.

Pattern 3: Incremental Refresh from a Changelog and a High-Water Mark

Pattern 3 keeps the precomputed table of Pattern 1 and the change awareness of Pattern 2, but moves the aggregation work off the write path. Writes append one small row to a changelog. A scheduled job reads the changelog past its last high-water mark, recomputes only the groups that changed, and advances the mark. This is incremental view maintenance done by hand.

The key design choice is to recompute each touched group from orders rather than apply deltas. Recomputing handles updates, deletes, MIN, MAX and COUNT(DISTINCT ...) correctly, and it is idempotent, so processing the same change twice is harmless.

CREATE TABLE orders_changelog (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_day   DATE            NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  changed_at  DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE = InnoDB;

CREATE TABLE mv_watermark (
  view_name VARCHAR(64)     NOT NULL PRIMARY KEY,
  last_id   BIGINT UNSIGNED NOT NULL
) ENGINE = InnoDB;

INSERT INTO mv_watermark VALUES ('daily_customer_totals', 0);

DELIMITER $$

-- Append-only: no hot rows, no read-modify-write on the write path.
CREATE TRIGGER orders_log_insert AFTER INSERT ON orders FOR EACH ROW
  INSERT INTO orders_changelog (order_day, customer_id)
  VALUES (DATE(NEW.created_at), NEW.customer_id)$$

CREATE TRIGGER orders_log_update AFTER UPDATE ON orders FOR EACH ROW
  INSERT INTO orders_changelog (order_day, customer_id)
  VALUES (DATE(OLD.created_at), OLD.customer_id),
         (DATE(NEW.created_at), NEW.customer_id)$$

CREATE TRIGGER orders_log_delete AFTER DELETE ON orders FOR EACH ROW
  INSERT INTO orders_changelog (order_day, customer_id)
  VALUES (DATE(OLD.created_at), OLD.customer_id)$$

CREATE PROCEDURE refresh_daily_customer_totals_incremental()
BEGIN
  DECLARE v_from BIGINT UNSIGNED;
  DECLARE v_to   BIGINT UNSIGNED;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    DO RELEASE_LOCK('refresh_daily_customer_totals');
    RESIGNAL;
  END;

  IF GET_LOCK('refresh_daily_customer_totals', 0) = 1 THEN
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

    SELECT last_id INTO v_from
      FROM mv_watermark WHERE view_name = 'daily_customer_totals';

    -- Stay behind the newest changes by a safety margin; see below.
    SELECT COALESCE(MAX(id), v_from) INTO v_to
      FROM orders_changelog
     WHERE id > v_from
       AND changed_at < NOW(6) - INTERVAL 30 SECOND;

    IF v_to > v_from THEN
      DROP TEMPORARY TABLE IF EXISTS touched;
      CREATE TEMPORARY TABLE touched (
        order_day   DATE            NOT NULL,
        customer_id BIGINT UNSIGNED NOT NULL,
        PRIMARY KEY (order_day, customer_id)
      );

      INSERT IGNORE INTO touched
      SELECT order_day, customer_id
        FROM orders_changelog
       WHERE id > v_from AND id <= v_to;

      START TRANSACTION;

      DELETE s
        FROM daily_customer_totals s
        JOIN touched t USING (order_day, customer_id);

      INSERT INTO daily_customer_totals (order_day, customer_id, order_count, total_amount)
      SELECT DATE(o.created_at), o.customer_id, COUNT(*), SUM(o.amount)
        FROM orders o
        JOIN touched t
          ON o.customer_id = t.customer_id
         AND o.created_at >= t.order_day
         AND o.created_at <  t.order_day + INTERVAL 1 DAY
       GROUP BY DATE(o.created_at), o.customer_id;

      UPDATE mv_watermark SET last_id = v_to
       WHERE view_name = 'daily_customer_totals';

      DELETE FROM orders_changelog WHERE id <= v_to;

      COMMIT;
      DROP TEMPORARY TABLE touched;
    END IF;

    DO RELEASE_LOCK('refresh_daily_customer_totals');
  END IF;
END$$

DELIMITER ;

CREATE EVENT refresh_daily_customer_totals_incr
  ON SCHEDULE EVERY 1 MINUTE
  ON COMPLETION PRESERVE
  DO CALL refresh_daily_customer_totals_incremental();

The delete and re-insert of each touched group happen in one transaction, so a reader under MVCC sees either the old totals or the new ones, never a gap. Refresh cost now scales with how much changed, not with table size, and the write path pays for one append instead of a read-modify-write on a contended row.

The safety margin is not optional. AUTO_INCREMENT values are assigned at insert time, but transactions commit in any order. If a long transaction takes changelog id 100 and commits after the refresh has already advanced the mark past 105, id 100 is skipped for good. The 30-second margin assumes no write transaction on orders stays open longer than that. Set it above your longest real transaction, or replace the id mark with a time-window scan that overlaps the previous run; because the recompute is idempotent, overlap is safe.

Why not just use updated_at? A high-water mark on orders.updated_at avoids the changelog triggers, and it works if orders are never hard-deleted and never change customer_id or created_at. A hard delete leaves no row to find, and a row that moves groups only reveals its new group, not the old one it left. The changelog records both.

Prior art. Justin Swanhart’s Flexviews did this generically in the MySQL 5.x era, reading the binlog instead of triggers to build a changelog and fast-refreshing views from it. It is no longer maintained, but the design (capture changes, then apply them to derived tables) is the same one the next section takes outside MySQL.

MySQL-Compatible Systems with Native Materialized Views

Several systems that speak the MySQL protocol ship materialized views, which is why their docs crowd the search results. They are different engines with their own operational models, so moving to one is a platform decision, not a MySQL setting.

Alibaba Cloud AnalyticDB for MySQL supports CREATE MATERIALIZED VIEW with complete and fast (incremental) refresh. Fast refresh requires binary logging on the base tables and runs on a schedule between 5 seconds and 5 minutes, for example REFRESH FAST NEXT now() + INTERVAL 10 second.

Alibaba Cloud PolarDB for MySQL offers incremental materialized views that refresh on its in-memory column index (IMCI) read-only nodes, with a minimum refresh interval of one second. Documented limits include single-table aggregation only, COUNT/SUM/AVG as the supported aggregates, and no subqueries, UNION, window functions or CTEs in the view.

OceanBase added materialized views in version 4.3, including in MySQL mode, with full and incremental refresh. Incremental refresh reads from a materialized view log on each base table, which records inserts, updates and deletes.

SingleStore is often listed here, but it has no CREATE MATERIALIZED VIEW; its CREATE VIEW is unmaterialized. The nearest feature is projections (8.5 and later): a second copy of a table’s columns with different sort and shard keys, updated in the same transaction as the table. Projections speed up lookups on alternate keys; they do not precompute aggregations or joins.

MySQL HeatWave, covered above, is the only Oracle MySQL offering with CREATE MATERIALIZED VIEW.

Pattern 4: CDC into a System That Maintains Views Incrementally

Patterns 1 through 3 all make MySQL do two jobs: run the application’s transactions and maintain derived tables from them. Pattern 4 separates them. MySQL stays the system of record and keeps doing only the first job. Its binlog already records every committed row change, so change data capture streams those changes into another system that owns the derived views.

MySQL 8.4 is mostly ready for this by default: binary logging is on, binlog_format defaults to ROW (the variable itself is deprecated with row-based logging as the path forward), and binlog_row_image defaults to FULL. Check before you connect anything, and create a dedicated user:

SHOW VARIABLES WHERE Variable_name IN
  ('log_bin', 'binlog_format', 'binlog_row_image', 'gtid_mode',
   'binlog_expire_logs_seconds');
-- Expect: log_bin = ON, binlog_format = ROW, binlog_row_image = FULL.
-- Keep enough binlog retention to cover a connector outage.

CREATE USER 'cdc_reader'@'%' IDENTIFIED BY 'change-me';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT
  ON *.* TO 'cdc_reader'@'%';

Those settings and privileges match what Debezium’s MySQL connector requires. Debezium reads the binlog and emits a change event per row, with before and after images and an operation code (c, u, d, or r for snapshot reads), usually into Kafka. From there you need something that turns a stream of row changes into maintained aggregates, which is where the choice of destination matters. A stream processor can do it, but then you own the processor, its state store and whatever serves the result to your application. Incremental-view engines such as Epsio and RisingWave also read MySQL’s binlog directly and keep SQL-defined results up to date as rows change.

Pattern 4 costs MySQL almost nothing beyond reading the binlog. It adds a pipeline, a second system to run and a lag between the MySQL commit and the derived result. What you gain is that updates, deletes and non-additive aggregates are the destination’s problem instead of your triggers’, and the derived views no longer compete with checkout for row locks.

Where Tacnode Fits

Tacnode fits Pattern 4 when the derived result feeds decisions, not just dashboards: fraud and risk checks, limits, eligibility, personalization, agent context. The outcome you get is derived views refreshed incrementally on a schedule you set in SQL, every few seconds where the decision needs it, served at the concurrency of your request path, next to the synced rows themselves, which any query reads on demand at their latest committed state.

MySQL keeps its role as the system of record. Tacnode ingests from it by CDC (full snapshot, then incremental from the binlog; the MySQL-to-Tacnode sync guide walks through the binlog_format, binlog_row_image and privilege setup). Nothing about your MySQL transactions changes. The decisions that read from Tacnode get accurate, current context, and what they conclude (a verdict, a hold, a limit) is written to Tacnode as context for the next decision.

The view itself is plain SQL. Tacnode presents the PostgreSQL wire protocol, so you define it and query it with psql or any Postgres driver:

CREATE MATERIALIZED VIEW daily_customer_totals
WITH (refresh_mode = 'incremental') AS
SELECT date_trunc('day', created_at) AS order_day,
       customer_id,
       COUNT(*)    AS order_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY date_trunc('day', created_at), customer_id;

REFRESH MATERIALIZED VIEW daily_customer_totals;

Each refresh applies only what changed since the last one instead of recomputing the whole view, so you can schedule it every few seconds where a full rebuild would run hourly or daily, and it doesn’t lock the table while it runs. The same definition handles the cases that made Patterns 2 and 3 fragile: updates that move rows between groups, deletes, and joins across tables synced from MySQL. See incremental materialized views for how incremental maintenance works and which query shapes it supports, and what a materialized view is for the general concept.

Which Pattern Should You Use?

When to stop emulating. The signal is usually not a single failure. It is the reconciliation job you added to catch trigger drift, the third summary table that doubled write latency, or the refresh interval you cannot shorten because the rebuild already takes longer than the interval. Once the derived tables cost MySQL more than the transactions they summarize, move them off MySQL.

Frequently Asked Questions