Tales From A Lazy Fat DBA

Hello Everyone..

Every DBA I know has a complicated relationship with the MySQL optimizer. You feed it a five-table join, it picks an order that made sense to somebody’s heuristics fifteen years ago, and you spend the next hour sprinkling STRAIGHT_JOIN and index hints around like seasoning until the plan stops embarrassing you. We’ve all done it. Some of us have done it this week.

So when MySQL 9.7 landed and I read that the hypergraph optimizer is now available in the server I can just download and run, I didn’t rush to write about it. I’ve been burned by optimizer release notes before. Instead I pulled 9.7.4 into a lab box, built a dataset, and spent an evening actually flipping the switch on and off to see what changes. This post is that evening, written down.

Short version: this isn’t a patch on the old optimizer. It’s a second, completely separate optimizer living inside the same server, with a different approach to join planning, a different cost model, and even a different look to its EXPLAIN output. It’s off by default, you can turn it on per session or even per query, and in the right workload the plans it finds are genuinely better. In the wrong one, you’ll want to know how to back out. We’ll cover both.

Everything below was run on a stock MySQL 9.7.4 server. No tuning, no tricks, default innodb_buffer_pool_size aside from what my little lab VM allows. Your numbers will differ; the plan shapes won’t.

So what is it, actually

The optimizer MySQL has carried around for decades builds left-deep plans: it lines tables up in a chain, mostly explored greedily, and once an index exists on a join column it leans hard toward nested-loop index lookups. That design was a great fit for OLTP point queries, and it’s exactly why your five-table reporting query sometimes ends up doing two hundred thousand single-row index lookups when a couple of scans and a hash table would have been kinder to the storage layer.

The hypergraph optimizer is not an upgrade to that code path. It’s a second planner sitting beside it. The name comes from how it models your query: tables become nodes, join predicates become edges in a hypergraph (an edge there can connect groups of tables, not just pairs, which is what lets it reason about tricky outer-join and multi-way conditions correctly). On top of that structure it runs a dynamic-programming search over join orders, and crucially it considers bushy plans, where two independently-built join results get joined to each other. The old planner structurally can’t produce most of those shapes.

It also carries its own cost model. You’ll spot it instantly in EXPLAIN, because every operator now shows a cost range:

-> Inner hash join (c.id = o.customer_id)  (cost=188516..239268 rows=29980)

The first number is roughly the cost to produce the first row, the second the cost to produce them all. That distinction matters for things like LIMIT, where a plan that’s slow to finish but quick to start is the right answer. The old single-number costs couldn’t express that.

Why a whole second optimizer instead of more patches? Because join ordering is the one thing you can’t retrofit. Twenty years of heuristics are load-bearing in the old code; every fix risks someone’s production plan. A parallel planner lets the new approach mature while the old one keeps doing its job, and lets you, the DBA, pick per session or per query. Starting with MySQL 9.7, that switch is in the server you’re already running.

Turning it on (and off, and on for just one query)

Out of the box it’s dormant. Check your server:

mysql> SELECT @@optimizer_switch LIKE '%hypergraph_optimizer=on%' AS hypergraph_on;
+---------------+
| hypergraph_on |
+---------------+
|             0 |
+---------------+

Flip it for your session only, which is where all experimentation should start:

SET SESSION optimizer_switch = 'hypergraph_optimizer=on';

Same syntax with GLOBAL applies it to new connections, and SET PERSIST survives a restart. Hold off on those until you’ve read the rollout section.

The option I’ve ended up using most is neither: the SET_VAR hint scopes it to a single statement. This is gold for testing, and later for pinning the new planner onto the one reporting query that benefits, without touching anything else:

EXPLAIN FORMAT=TREE
SELECT /*+ SET_VAR(optimizer_switch='hypergraph_optimizer=on') */
       COUNT(*)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Pune';

-> Aggregate: count(0)  (cost=72208..72208 rows=1)
    -> Nested loop inner join  (cost=2.28..68363 rows=29980)
        -> Filter: (c.city = 'Pune')  (cost=10.1..49255 rows=4892)
            -> Index scan on c using PRIMARY  (cost=0.995..46437 rows=48917)
        -> Covering index lookup on o using idx_cust (customer_id = c.id)  (cost=0.637..3.91 rows=6.13)

Which brings up the obvious question: how do you know which optimizer produced a plan? The tell is right there in the output. Cost ranges (cost=2.28..68363) mean hypergraph; a single cost number means the classic planner. Notice the example above still chose a nested-loop join. The new optimizer isn’t allergic to nested loops; it just doesn’t default to them. Here the loop genuinely is the cheapest plan, and it said so.

EXPLAIN FORMAT=JSON changes shape too. Instead of the familiar query_block document you get a leaner tree of operators under query_plan, with inputs, access_type and estimated_rows fields. If you have monitoring that parses explain JSON, that’s your first migration task right there.

A playground with enough rows to matter

You can’t judge a join planner on four-row demo tables, so I built a small sales schema and loaded it with enough data that plan choices show up in wall-clock time: 50,000 customers, 1,000 products, 300,000 orders and 900,000 order items.

CREATE TABLE customers (
  id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, city VARCHAR(24) NOT NULL,
  segment ENUM('consumer','smb','enterprise') NOT NULL, created DATE NOT NULL
);
CREATE TABLE products (
  id INT PRIMARY KEY, sku VARCHAR(20) NOT NULL, category VARCHAR(16) NOT NULL,
  price DECIMAL(8,2) NOT NULL
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL,
  status ENUM('open','shipped','cancelled') NOT NULL,
  KEY idx_cust (customer_id), KEY idx_date (order_date)
);
CREATE TABLE order_items (
  id INT PRIMARY KEY, order_id INT NOT NULL, product_id INT NOT NULL,
  qty INT NOT NULL, unit_price DECIMAL(8,2) NOT NULL,
  KEY idx_order (order_id), KEY idx_prod (product_id)
);

For loading I used the old cross-join trick: a ten-row digits table multiplied against itself into a million-row seq table, then INSERT ... SELECT with a bit of modular arithmetic to spread cities, dates and statuses around. One table as a sample, the rest follow the same pattern:

CREATE TABLE digits (d INT PRIMARY KEY);
INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE seq (n INT PRIMARY KEY) AS
  SELECT a.d + b.d*10 + c.d*100 + e.d*1000 + f.d*10000 + g.d*100000 AS n
  FROM digits a, digits b, digits c, digits e, digits f, digits g;
INSERT INTO orders
SELECT n+1, 1 + (n * 7919) MOD 50000,
       DATE_ADD('2024-01-01', INTERVAL n MOD 680 DAY),
       ELT(1 + n MOD 10, 'open','shipped','shipped','shipped','shipped',
           'shipped','shipped','shipped','shipped','cancelled')
FROM seq WHERE n < 300000;

The one step people skip and then blame the optimizer for: statistics.

ANALYZE TABLE customers, products, orders, order_items;

Both planners read the same InnoDB statistics. Feed them stale numbers and they’ll both lie to you, just in different accents. Every run below was done warm (query executed a couple of times first), so we’re comparing plans, not buffer pool luck.

Reading the new plans without squinting

Let’s take a bread-and-butter reporting query: revenue by customer segment and product category, for shipped orders from one city since July.

SELECT c.segment, p.category, ROUND(SUM(oi.qty * oi.unit_price),2) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
JOIN products p     ON p.id          = oi.product_id
WHERE o.order_date >= '2025-07-01'
  AND o.status = 'shipped'
  AND c.city = 'Montreal'
GROUP BY c.segment, p.category;

The classic planner, EXPLAIN FORMAT=TREE:

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Nested loop inner join  (cost=22819 rows=11232)
            -> Nested loop inner join  (cost=18887 rows=11232)
                -> Nested loop inner join  (cost=14956 rows=3757)
                    -> Filter: (c.city = 'Montreal')  (cost=4932 rows=4892)
                        -> Table scan on c  (cost=4932 rows=48917)
                    -> Filter: ((o.order_date >= DATE'2025-07-01') and (o.`status` = 'shipped'))  (cost=1.46 rows=0.768)
                        -> Index lookup on o using idx_cust (customer_id = c.id)  (cost=1.46 rows=5.85)
                -> Index lookup on oi using idx_order (order_id = o.id)  (cost=0.747 rows=2.99)
            -> Single-row index lookup on p using PRIMARY (id = oi.product_id)  (cost=0.25 rows=1)

Four tables, one chain of nested loops, every join via index lookup. The default planner’s comfort food.

Same query, same session, after SET SESSION optimizer_switch='hypergraph_optimizer=on':

-> Table scan on <temporary>  (cost=216016..216388 rows=2237)
    -> Aggregate using temporary table  (cost=216015..216015 rows=2237)
        -> Inner hash join (p.id = oi.product_id)  (cost=1057..205543 rows=11779)
            -> Nested loop inner join  (cost=17.2..202633 rows=11779)
                -> Nested loop inner join  (cost=38.8..152718 rows=3933)
                    -> Filter: (c.city = 'Montreal')  (cost=10.5..51509 rows=4892)
                        -> Index scan on c using PRIMARY  (cost=0.995..48692 rows=48917)
                    -> Filter: ((o.order_date >= DATE'2025-07-01') and (o.`status` = 'shipped'))  (cost=20.7..20.7 rows=0.804)
                        -> Index lookup on o using idx_cust (customer_id = c.id)  (cost=3.3..20.2 rows=6.13)
                -> Index lookup on oi using idx_order (order_id = o.id)  (cost=4.24..12.7 rows=3)
            -> Hash
                -> Index scan on p using PRIMARY  (cost=0.691..691 rows=1000)

Three things worth your attention here.

It kept most of the old plan. Customers filtered first, then orders, then items … the new planner independently arrived at the same join order for the lower three tables. When the old plan was fine, the new one tends to agree. That alone should lower your blood pressure about enabling it.

It changed the one piece that deserved changing. The classic plan probes the products primary key once per item row … 17,199 single-row lookups in this run. The hypergraph plan reads all 1,000 products once, builds a hash table, and streams item rows past it. Same answer, far fewer storage engine round trips for that join.

The estimates are still estimates. It predicted 2,237 groups out of this aggregation; the real number is 18. Costing has improved, clairvoyance hasn’t. Keep reading plans with the same skepticism you always have.

On the clock, this one’s nearly a wash on my warm little box: 172 ms classic, 162 ms hypergraph, end to end in EXPLAIN ANALYZE. The section was about reading the output. The next one is about winning.

Where it wins, with a stopwatch

Here’s the query shape that made me sit up. A fat date range instead of a selective city filter … the kind of thing month-end reports are made of:

SELECT c.segment, COUNT(DISTINCT o.id) AS orders,
       ROUND(SUM(oi.qty * oi.unit_price),2) AS revenue
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.order_date >= '2025-06-01'
GROUP BY c.segment;

That date predicate matches 71,883 orders … about a quarter of the table. Watch what the classic planner does with it, EXPLAIN ANALYZE, warm cache:

-> Group aggregate: sum(tmp_field), count(distinct orders.id)  (actual time=1111..1155 rows=3 loops=1)
    -> Sort: c.segment  (actual time=1088..1099 rows=215649 loops=1)
        -> Stream results  (cost=224955 rows=417116) (actual time=2.14..1024 rows=215649 loops=1)
            -> Nested loop inner join  (cost=224955 rows=417116) (actual time=2.13..899 rows=215649 loops=1)
                -> Nested loop inner join  (cost=78965 rows=139522) (actual time=2.1..288 rows=71883 loops=1)
                    -> Filter: (o.order_date >= DATE'2025-06-01')  (cost=30132 rows=139522) (actual time=2.08..104 rows=71883 loops=1)
                        -> Table scan on o  (cost=30132 rows=299796) (actual time=1.96..81.2 rows=300000 loops=1)
                    -> Single-row index lookup on c using PRIMARY (id = o.customer_id)  (cost=0.25 rows=1) (actual time=0.0023..0.00234 rows=1 loops=71883)
                -> Index lookup on oi using idx_order (order_id = o.id)  (cost=0.747 rows=2.99) (actual time=0.00727..0.00806 rows=3 loops=71883)

Read those loops numbers. It probed the customers primary key 71,883 times, then probed idx_order on the items table another 71,883 times. Each probe is cheap; seventy-two thousand of anything is not. Total: 1.155 seconds, and on a cold cache with real random I/O this pattern gets much uglier than my warm numbers show.

Same query under the hypergraph optimizer:

-> Group aggregate: sum((oi.qty * oi.unit_price)), count(distinct o.id)  (cost=1.18e+6..1.27e+6 rows=221) (actual time=516..584 rows=3 loops=1)
    -> Sort: c.segment  (cost=1.18e+6..1.18e+6 rows=417886) (actual time=472..493 rows=215649 loops=1)
        -> Inner hash join (oi.order_id = o.id)  (cost=271333..784737 rows=417886) (actual time=104..422 rows=215649 loops=1)
            -> Index scan on oi using PRIMARY  (cost=0.424..380688 rows=897926) (actual time=0.68..138 rows=900000 loops=1)
            -> Hash
                -> Inner hash join (c.id = o.customer_id)  (cost=62789..223884 rows=139522) (actual time=12.8..95.9 rows=71883 loops=1)
                    -> Filter: (o.order_date >= DATE'2025-06-01')  (cost=0.995..138846 rows=139522) (actual time=0.983..61.3 rows=71883 loops=1)
                        -> Index scan on o using PRIMARY  (cost=0.406..121576 rows=299796) (actual time=0.925..44.4 rows=300000 loops=1)
                    -> Hash
                        -> Index scan on c using PRIMARY  (cost=0.949..46437 rows=48917) (actual time=0.0858..7.72 rows=50000 loops=1)

584 ms. Half the time, same rows out.

And look at the shape. This is a bushy plan: it built orders ⋈ customers as one hash join (filtered orders on the build side, one clean scan of customers), kept that result in a hash table, and streamed all 900,000 item rows past it in a single pass. Three sequential scans, two hash tables, zero per-row index probes. loops=1 on every line.

This is the philosophical difference in one picture. The classic planner saw an index on customer_id and an index on order_id and couldn’t resist using them row by row. The hypergraph planner priced the alternative … scan everything once, join in memory … and noticed it was cheaper at this selectivity. Nobody hinted anything. It just did the arithmetic.

A quarter of the orders table was the tipping point here. Drop the date filter to a few days and the nested loops win again, and the hypergraph planner happily picks them, as we saw in the Pune example earlier. The point isn’t “hash joins good, loops bad.” The point is the planner now genuinely considers both, on shapes the old one couldn’t even represent.

The honest section: what to watch out for

I went into this expecting a list of “not supported yet” errors to warn you about. I came back nearly empty-handed. On 9.7.4 I deliberately threw the awkward stuff at it with the switch on … recursive CTEs, window functions, GROUP BY ... WITH ROLLUP, JSON_TABLE, LATERAL derived tables, multi-table UPDATE, SELECT ... FOR UPDATE, even full-text MATCH ... AGAINST … and every single one ran and returned correct results. Whatever list of unsupported corners existed in earlier builds, I couldn’t trip over it in an evening of trying. Test your own workload, obviously, but don’t let “it’s probably half-finished” be the excuse.

The real catches are quieter:

Your hint toolbox loses its grip. This is the one that will bite working DBAs. STRAIGHT_JOIN and JOIN_ORDER hints execute without errors or warnings under the hypergraph planner, but in my tests they didn’t change the plan. I asked for orders-before-customers with both; the plan that came back was identical to the unhinted one each time. Years of carefully placed join-order hints in your application? Under the new planner, assume they’re decorative until you verify otherwise. SET_VAR hints work fine … that’s how you control the optimizer choice itself.

Estimates can still be far off. You saw the aggregation estimate of 2,237 groups against an actual 18. A better search over a wrong cardinality model can still pick a wrong plan. The usual medicine applies: fresh statistics, and histograms on skewed columns.

Some queries get slower, and nobody can tell you which in advance. My Montreal query was a coin flip (162 vs 172 ms). Others in my scratchpad came out a few percent behind the classic planner. Different cost model, different tie-breaks; a workload that spent years being unconsciously tuned around the old planner’s habits has landmines in it. That, more than missing features, is why the switch ships in the off position.

Plan-dependent tooling needs a look. Anything parsing EXPLAIN FORMAT=JSON output will meet the new query_plan structure. Anything diffing plan fingerprints will see cost ranges where it expected single numbers.

Rolling it out like an adult

The temptation after seeing a 2x win is SET PERSIST optimizer_switch='hypergraph_optimizer=on' on a Friday. Resist. Here’s the sequence I’d actually run in a real shop.

1. Replay your top queries in a lab or on a replica, session-scoped. Pull the heavy hitters from performance_schema.events_statements_summary_by_digest (sort by SUM_TIMER_WAIT, take the top 30 or so). Run each with EXPLAIN ANALYZE twice per optimizer, warm, and write the numbers down. You’re building a simple three-column table: digest, classic time, hypergraph time.

SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 30;

2. Pin the winners with SET_VAR, leave everything else alone. The per-statement hint means you don’t need a server-wide decision to start collecting the benefit. The month-end report that went from 1.2 s to 0.6 s gets its hint; the OLTP path stays on the planner it’s been happy with for years:

SELECT /*+ SET_VAR(optimizer_switch='hypergraph_optimizer=on') */
       c.segment, COUNT(DISTINCT o.id), ...

3. Graduate to session-level for whole workloads that profit. Reporting users, ETL accounts and read-only analytics connections are natural candidates … set it in the connection setup of those jobs, not globally. Your application’s point lookups gain nothing from a smarter join search; they don’t need to be part of the experiment.

4. Only then consider GLOBAL/PERSIST, once a full business cycle (including that one monster quarter-close query someone wrote in 2019) has run clean. And keep the escape hatch written down where 3 AM you can find it:

SET GLOBAL optimizer_switch = 'hypergraph_optimizer=off';

5. Re-run the comparison after every upgrade. Both optimizers keep evolving, and a plan that was a wash last release may be a win or a regression in the next. The ten-minute digest replay is cheap insurance.

One process note: because the switch lives in optimizer_switch, it plays nicely with everything you already use for staged changes … per-user init_connect tricks, proxy-level session settings, connection pool configs. You almost certainly already have the machinery to roll this out gradually. Use it.

I’ve spent a good part of my career negotiating with query optimizers, and the negotiation usually goes one way: the planner has a blind spot, and I paper over it with hints, rewrites, or the occasional covering index built purely to bribe it. What struck me most in this evening of testing wasn’t the 2x number. It was the Pune query … the one where the hypergraph planner looked at a nested loop, agreed with the old optimizer completely, and moved on. A planner that knows when not to be clever is the one I’ll eventually trust with the global switch.

We’re not at “flip it everywhere” yet. The hint behaviour alone means any shop with a history of join-order surgery needs to retest before leaning on it. But the direction is unmistakable: MySQL now has a planner that can represent the plans analytic queries actually need, and it’s one SET statement away on a stock server.

If you want to try this on your own workload, the whole experiment fits in a lunch break:

• Build or borrow a dataset with real row counts; ANALYZE TABLE everything.
• Pull your top 30 digests from performance_schema and race them: classic vs SET_VAR hypergraph, EXPLAIN ANALYZE, warm runs.
• Pin wins per query with SET_VAR, note the losses, and check whether any of your join hints still do anything.
• Re-race after your next upgrade.

All plans and timings in this post came from a stock MySQL 9.7.4 on a modest lab VM … expect your absolute numbers to differ, and the I/O-bound gaps to be wider than mine. If you run the comparison on your own workload, I’d genuinely like to hear which way it went: the comments on fatdba.com are open, and the counterexamples are usually where the interesting posts come from.

Happy flipping. Carefully.

Hope It Helped!
Prashant Dixit