Aurora MySQL 8.0 Optimizer Hints That Actually Change Execution Plans

Why Hints Are Sometimes the Right Tool

The standard advice on optimizer hints is correct: fix the root cause — missing indexes, stale statistics, bad schema design — before reaching for hints. Hints are maintenance debt. They freeze a plan that the optimizer might improve on its own as data characteristics change, and they don't survive schema refactoring cleanly.

That said, there are legitimate scenarios where hints are the right tool. A query that performs well on 95% of parameter values but catastrophically on 5% due to parameter sniffing. A join order that the optimizer consistently gets wrong because the statistics for an intermediate result set are inaccurate. A subquery unnesting that creates a worse plan than the original correlated form. In these cases, a precisely targeted hint is cleaner than a workaround schema change or a session-level optimizer flag.

The problem on Aurora MySQL 8.0 is that the hint documentation covers far more hints than actually produce reliable plan changes. This article covers the subset that demonstrably works.

The New Hint Syntax: Structured vs Legacy

MySQL 5.x used comment-embedded index hints: USE INDEX, FORCE INDEX, IGNORE INDEX. These are still valid in MySQL 8.0 and Aurora MySQL 8.0, but they only affect index selection — not join order, join algorithm, subquery strategy, or any other optimizer decision.

MySQL 8.0 introduced structured optimizer hints using the /*+ ... */ syntax placed immediately after the SELECT, INSERT, UPDATE, or DELETE keyword. These hints cover a much wider range of optimizer decisions:

-- Legacy index hint (still works, limited scope)
SELECT * FROM orders USE INDEX (idx_customer_date)
WHERE customer_id = 42 AND created_at > '2023-01-01';

-- Structured hint syntax (MySQL 8.0+)
SELECT /*+ INDEX(orders idx_customer_date) */ *
FROM orders
WHERE customer_id = 42 AND created_at > '2023-01-01';

-- Multiple hints in one block
SELECT /*+ JOIN_ORDER(orders, customers) BKA(orders) */ *
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.region = 'west';

JOIN_ORDER: The Most Reliable Plan-Changing Hint

JOIN_ORDER(t1, t2, t3) fixes the order in which the optimizer joins tables. This is the hint that most reliably changes execution plans on Aurora MySQL 8.0 when the optimizer is choosing a join order that leads to a large intermediate result set.

The scenario where this matters: a query joining three tables where the optimizer correctly identifies the most selective filter on table A but chooses to process table B first due to a cost estimate error on the join selectivity between B and C.

-- Problem query: optimizer joins payments first (large table), then orders, then customers
-- Resulting in a 5M row intermediate result set filtered down to 200 rows at the end
EXPLAIN FORMAT=JSON
SELECT c.name, o.order_date, SUM(p.amount)
FROM payments p
JOIN orders o ON p.order_id = o.id
JOIN customers c ON o.customer_id = c.id
WHERE c.tier = 'enterprise'
  AND o.status = 'completed'
  AND p.created_at > '2023-01-01'
GROUP BY c.name, o.order_date;

-- With JOIN_ORDER hint: start with the most selective filter (customers.tier)
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(c, o, p) */
  c.name, o.order_date, SUM(p.amount)
FROM payments p
JOIN orders o ON p.order_id = o.id
JOIN customers c ON o.customer_id = c.id
WHERE c.tier = 'enterprise'
  AND o.status = 'completed'
  AND p.created_at > '2023-01-01'
GROUP BY c.name, o.order_date;

Before: rows_examined ~5,200,000. After JOIN_ORDER: rows_examined ~18,400. Execution time: 4.2s → 0.08s. This is the class of problem where JOIN_ORDER pays off — not marginal improvements, but order-of-magnitude gains when the optimizer has a join order cost estimate that's badly wrong.

NO_SEMIJOIN and SEMIJOIN: Controlling Subquery Execution Strategy

MySQL's optimizer can execute IN (subquery) and EXISTS (subquery) patterns in several ways: as a semijoin (most efficient for many cases), as a materialized subquery, or as a correlated subquery. The optimizer chooses based on statistics, and it frequently chooses wrong when the subquery produces a small result set that the outer query filters heavily.

-- Optimizer unnests this IN subquery into a semijoin
-- and produces a bad plan when the subquery result is small
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE tier = 'enterprise' AND region = 'west'
);

-- Force materialization of the subquery result (good when subquery is small and stable)
SELECT /*+ SEMIJOIN(@subq MATERIALIZATION) */ * FROM orders
WHERE customer_id IN (
  SELECT /*+ QB_NAME(subq) */ id
  FROM customers WHERE tier = 'enterprise' AND region = 'west'
);

-- Disable semijoin transformation entirely (fall back to correlated execution)
SELECT /*+ NO_SEMIJOIN(@subq DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION) */
  * FROM orders
WHERE customer_id IN (
  SELECT /*+ QB_NAME(subq) */ id
  FROM customers WHERE tier = 'enterprise' AND region = 'west'
);

The QB_NAME() hint is required to reference a specific query block from the outer hint. Without it, the optimizer doesn't know which subquery the SEMIJOIN hint applies to in a query with multiple subqueries.

HASH_JOIN and NO_HASH_JOIN

Aurora MySQL 8.0 supports hash joins for equi-joins where no useful index exists on one side of the join. The optimizer enables hash join automatically in many cases, but it can be explicitly requested or suppressed.

-- Force hash join for a specific table pair
-- Useful when optimizer incorrectly estimates join selectivity and chooses nested loop
SELECT /*+ HASH_JOIN(o, li) */
  o.id, o.customer_id, COUNT(li.id) AS line_count
FROM orders o
JOIN line_items li ON o.id = li.order_id
WHERE o.created_at BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY o.id, o.customer_id;

-- Suppress hash join (revert to nested loop or BNL)
-- Useful when hash join is chosen but memory pressure is causing disk spills
SELECT /*+ NO_HASH_JOIN(o, li) */
  o.id, o.customer_id, COUNT(li.id) AS line_count
FROM orders o
JOIN line_items li ON o.id = li.order_id
WHERE o.created_at BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY o.id, o.customer_id;

Hash join is most beneficial for large equi-joins with no supporting index. The risk on Aurora MySQL 8.0: hash join builds the entire build-side input in memory (controlled by join_buffer_size). If the build side is larger than join_buffer_size, it spills to disk and becomes slower than a well-indexed nested loop join. Always check EXPLAIN ANALYZE output for "disk" in the hash join node before assuming hash join is beneficial.

MERGE and NO_MERGE for Derived Tables

MySQL 8.0 can merge derived tables (subqueries in FROM clauses) into the outer query, eliminating the materialization cost. This is usually beneficial — it allows the optimizer to push predicates into the derived table and use indexes more effectively. But sometimes the optimizer incorrectly merges a derived table that should be materialized, creating a worse plan.

-- Problem: optimizer merges this derived table and loses the benefit
-- of the pre-aggregated result set
SELECT customer_id, total_orders, avg_order_value
FROM (
  SELECT customer_id,
         COUNT(*) AS total_orders,
         AVG(amount) AS avg_order_value
  FROM orders
  WHERE created_at > '2023-01-01'
  GROUP BY customer_id
) summary
WHERE total_orders > 10;

-- Force materialization of the derived table
-- The GROUP BY result is computed once and scanned once with the WHERE filter applied after
SELECT /*+ NO_MERGE(summary) */ customer_id, total_orders, avg_order_value
FROM (
  SELECT customer_id,
         COUNT(*) AS total_orders,
         AVG(amount) AS avg_order_value
  FROM orders
  WHERE created_at > '2023-01-01'
  GROUP BY customer_id
) summary
WHERE total_orders > 10;

SET_VAR: Session Variable Override per Query

One of the most practically useful hint types on Aurora MySQL 8.0: SET_VAR overrides a session variable for the duration of a single query execution. This is cleaner than setting session variables before each query and avoids the risk of a variable not being reset if the application code path exits early.

-- Override sort_buffer_size for a single large sort operation
SELECT /*+ SET_VAR(sort_buffer_size = 16777216) */
  customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

-- Increase join_buffer_size for a specific hash join heavy query
SELECT /*+ SET_VAR(join_buffer_size = 33554432) HASH_JOIN(o, p) */
  o.id, p.amount
FROM orders o
JOIN payments p ON o.id = p.order_id
WHERE o.status = 'completed';

-- Disable hash join globally but enable for one specific expensive query
-- (set optimizer_switch disabling hash_join at session level,
-- then re-enable for this query only)
SELECT /*+ SET_VAR(optimizer_switch='hash_join=on') HASH_JOIN(a, b) */
  a.id, b.value
FROM table_a a JOIN table_b b ON a.key = b.key;

Hints That Look Useful But Don't Reliably Work on Aurora

These hints appear in the MySQL 8.0 documentation but produce no consistent plan change on Aurora MySQL 8.0 in our testing:

• BKA / NO_BKA — Batched Key Access join. Requires Multi-Range Read (MRR) to be enabled. On Aurora, the storage layer behaves differently from traditional InnoDB on local disk, and MRR optimization often has no effect. BKA hints frequently do nothing measurable.
• BNL / NO_BNL — Block Nested Loop. Effectively superseded by hash join in MySQL 8.0.18+. Aurora MySQL 8.0 uses hash join instead of BNL for most workloads where BNL would previously have applied. BNL hints may be silently converted to hash join behavior.
• MRR / NO_MRR — Multi-Range Read. On Aurora's distributed storage architecture, the read-ahead benefit of MRR is different from local disk. In practice, MRR hints have minimal measurable effect on Aurora query plans.

Validating That a Hint Is Taking Effect

Always verify hints with EXPLAIN FORMAT=JSON and look for the hint-changed plan indicators:

-- EXPLAIN FORMAT=JSON shows whether hints were applied
EXPLAIN FORMAT=JSON
SELECT /*+ JOIN_ORDER(c, o, p) NO_SEMIJOIN(@sub DUPSWEEDOUT) */
  c.name, SUM(p.amount)
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN payments p ON o.id = p.order_id
WHERE c.tier = 'enterprise'\G

-- Look for "hint" in the JSON output
-- "attached_condition" shows pushed predicates
-- "using_join_buffer" shows join algorithm
-- Absent expected node = hint may be ignored

-- EXPLAIN ANALYZE for actual execution stats (Aurora MySQL 8.0.26+)
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(c, o, p) */
  c.name, SUM(p.amount)
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN payments p ON o.id = p.order_id
WHERE c.tier = 'enterprise'\G

If EXPLAIN ANALYZE shows the same plan with and without the hint, the hint is being ignored — either because it conflicts with another constraint, references a table alias that doesn't match exactly, or addresses an optimizer decision that Aurora's version doesn't support for that code path. Hints fail silently in MySQL; there's no error or warning when they have no effect.