I Hit MySQL's Missing Materialized View Problem. Here's How CDC Helped.

MySQL, unlike Postgres, has no Materialized View. If you have an expensive join-and-aggregate query that's too slow to run on every request, you're on your own to build something that mimics one.

I ran into this on a project with a complex query touching hundreds of thousands of rows across several tables. Run live, it was too slow for a regular endpoint. Left alone, it created three separate problems at once. Slow execution on every request. A real cost on the database instance from running that query repeatedly. And staleness once we tried to cache or schedule around it instead.

The usual workaround is a poor-man's materialized view, a summary table kept up to date by application code instead of the database. I'd built exactly this before. A write to a dependency table set a "dirty" flag. A checker running every 5 minutes looked for that flag, and only recomputed and swapped in fresh rows when it found one. It worked, but it had two problems that got worse as things grew.

Fixed staleness was one. Even with the flag doing its job, nothing updates faster than the 5-minute poll interval, no matter how urgent the change.

Coordination cost across instances was the other. With multiple app instances all running the same checker independently, they'd all see the same flag and all race to recompute and write. That's duplicate work at best, contention at worst. And the flag itself, if not scoped precisely to the affected rows, can still mean recomputing more than necessary per cycle.

CDC , short for change data capture, solves both, for a different reason than you'd expect. It isn't just "faster polling." It's reacting to individual row changes as they happen, straight off the database's own change log.

The core idea

MySQL's binary log, or binlog, already records every row-level change once binlog_format=ROW is enabled. A CDC tool tails that log and turns each change into an event, one per row, not per SQL statement and not per table. A single UPDATE touching 10,000 rows produces 10,000 individual events, not one.

I used Debezium, a CDC tool, in embedded mode. It runs as a plain dependency inside a Java process, reading the binlog directly. No Kafka, no Kafka Connect cluster. Offsets and schema history are just local files.

No Kafka mattered more than it might sound like. There was no infrastructure budget or approval to stand up a message broker just to solve one table's staleness problem. Kafka is the standard pairing for Debezium, and for good reason at scale, but it's real infrastructure to run, monitor, and justify. Embedded mode gets you the same underlying CDC mechanism with nothing new to operate beyond the app itself.

What I built

To show the pattern end to end, I put together a small standalone sample. It's split into two independent Spring Boot apps that only share the MySQL database. api is a normal CRUD service with no idea CDC exists. sync is a separate, standalone process running the Debezium embedded engine, watching the binlog and keeping a summary table up to date.

That split matters more than it looks. Embedding Debezium inside the API app works for a demo, but breaks the moment you scale the API horizontally, since every instance would start its own binlog reader and duplicate the work. Keeping sync as its own single-instance process avoids that entirely.

I also structured sync so a new materialized view is just a new class implementing one small interface, not a change to the Debezium setup itself. The full sample, including that pattern, is on GitHub.

The rough edges

A few things weren't obvious going in.

• Debezium Embedded still demands a bootstrap.servers property even with zero Kafka involved, a leftover from the Kafka Connect config class it reuses internally. A dummy value satisfies it.

• A pinned Debezium version can silently mismatch a transitively-resolved Kafka client version, producing a NoSuchMethodError at runtime. Pinning kafka-clients explicitly fixed it.

• A non-web Spring Boot process has nothing keeping the JVM alive by default. The Debezium engine's thread has to be explicitly non-daemon, or the process exits right after startup, silently, with no error.

None of these are deal-breakers, just the kind of thing you only find by running it.

Was it worth it over the old polling approach?

For my case, yes. The two things I actually needed were finer-grained updates and no more redundant work across app instances, and CDC gives both directly. If your staleness tolerance is already loose and your poller only recomputes small, correctly-scoped slices, the old approach might genuinely be fine. CDC is a real upgrade specifically when polling either can't keep up or is quietly doing more work than it needs to.