MySQL 9.7 LTS: Hypergraph Optimizer Joins Community

MySQL 9.7 LTS landed on April 21 — the first long-term support release since MySQL 8.4 — and this time Oracle brought actual gifts. The hypergraph optimizer, previously locked behind the Enterprise Edition paywall, is now available in the Community Edition every developer runs in production. So is full DML support for JSON Duality Views. This is a meaningful shift, and it did not happen by accident.

The Hypergraph Optimizer: What It Is, and Why It Was Hidden

MySQL’s classic join optimizer is built on a left-deep search model: it evaluates join orders as a left-to-right sequence, which made sense in 1995 and limits it today. Complex queries with multiple joined tables, CTEs, or window functions often end up with suboptimal plans because the search space gets pruned too aggressively.

The hypergraph optimizer treats a query differently. It models the problem as a graph — tables are nodes, join predicates are edges — then uses dynamic programming to explore a wider range of plan shapes, including bushy joins that the classic optimizer would never consider. The result is better cost-based decisions between nested-loop and hash joins, and real awareness of how row ordering in one part of a plan affects the cost of later operations.

Enabling it takes one line:

SET SESSION optimizer_switch='hypergraph_optimizer=on';

That is a session-scoped change, which is the right place to start. Community benchmarks show the gains are real for complex workloads: a CTE-heavy query dropped from 2.1 seconds to 1.6 seconds (~24% faster); an aggregation query with joins went from 0.52s to 0.36s (~17% faster). Simple OLTP lookups can actually regress — one test saw a query slow from 0.0086s to 0.0767s. Peter Zaitsev of Percona put it plainly: “make sure to test how it impacts your application, do not just assume it is newer… so it must be better.”

One practical catch: the hypergraph optimizer only supports EXPLAIN FORMAT=TREE and JSON v2 output. If you have tooling that parses traditional EXPLAIN output, update it before enabling hypergraph globally.

-- Verify the optimizer is active
SHOW VARIABLES LIKE 'optimizer_switch';

-- EXPLAIN requires TREE or JSON format
EXPLAIN FORMAT=TREE SELECT o.id, c.name, COUNT(i.id)
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, c.name;

JSON Duality Views: DML Without a License

JSON Duality Views have been part of MySQL since 9.0, but Community Edition users could only query them — INSERT, UPDATE, and DELETE operations required Enterprise Edition. That restriction is gone in 9.7. Community Edition now supports full DML on JSON Duality Views, including auto-increment support so you no longer need to manually wire up primary keys on insert.

If you are not familiar with JSON Duality Views, the short version: they let you expose the same relational data as JSON documents without duplicating it. Your application can choose document-style JSON access or classic SQL access against the same underlying tables. It is a pragmatic bridge for teams using both document and relational patterns in the same stack.

Why Oracle Did This

The honest answer is PostgreSQL. Developer surveys in 2026 show PostgreSQL adoption at 55.6% and climbing, while MySQL is declining. The MySQL community has not been quiet about it — Percona published an open letter to Oracle calling for action. Community members publicly raised the question of whether MySQL was becoming irrelevant for new projects.

Oracle has held three public community discussions since then, and MySQL 9.7 is the clearest output: five formerly Enterprise-only components now ship in Community Edition, including replication applier metrics, group replication flow control statistics, and the telemetry component. Mike Frank, Oracle’s MySQL product management director, framed it as building “a tighter feedback loop with the community.”

Whether you read that as genuine commitment or damage control, the outcome for developers is the same: features you were paying for — or not using because of the cost — are now free.

Should You Upgrade?

If you are on MySQL 8.4, a direct upgrade to 9.7 is supported. From MySQL 8.0, you go through 8.4 first. From any 9.x Innovation release, 9.7 accepts a direct upgrade. The full MySQL 9.7.0 release notes cover every change.

The practical recommendation: upgrade to 9.7, but do not touch the optimizer switch in production until you have tested your most complex queries in staging with hypergraph_optimizer=on. Enable it globally only after you have validated that nothing regresses. For simple CRUD applications, the default optimizer is already well-tuned. The hypergraph optimizer earns its keep at join depth of four or more tables, or anywhere CTEs and window functions appear in the same query.

One LTS cycle of development, and MySQL Community Edition just got noticeably better at its most common bottleneck. That is worth taking five minutes to test.