Resolving query plan regressions after a MySQL engine upgrade

When upgrading an Amazon Aurora MySQL or Amazon RDS for MySQL major version, some queries might experience performance regressions. Queries that previously ran in milliseconds may slow down to several seconds, even when the application code, schema, and traffic patterns remain unchanged. These regressions might appear only under production load, hours after the upgrade completes.

We see this pattern most often with major version upgrades. A major version jump (for example, 8.0 to 8.4) accumulates years of optimizer changes in a single step: revised cost models, new default parameter values, expanded execution strategies, and updated character set defaults. Queries that were stable for years can receive a materially different execution plan without any application change.

In this post, we walk you through the diagnostic workflow we apply when a query regresses after a major version upgrade. Each section is tied to a specific change introduced between MySQL versions, so you can identify the exact mechanism responsible and apply the right fix.

Why performance changes after an upgrade

Before you diagnose a specific query, it helps to understand what actually changed in the engine. Three categories of change drive most post-upgrade regressions:

Optimizer query cost estimation and flag defaults: Each MySQL 8.0.x release revises how the optimizer estimates the cost of candidate query execution plans, and in some releases also flips new optimizer_switch flags to on by default. If you upgraded from 8.0.26 or earlier, the prefer_ordering_index (8.0.27) flag is now enabled by default in your environment. If you upgraded from 8.0.17 or earlier, expanded hash join eligibility (8.0.18, extended in 8.0.20) is now enabled by default. Because the underlying cost model changed, the optimizer now picks different execution plans by default, sometimes considering strategies your previous version never evaluated at all.

Default character set and collation: MySQL 8.0 and 8.4 both default to utf8mb4 with collation_server = utf8mb4_0900_ai_ci, so a straight 8.0-to-8.4 upgrade doesn’t change this default. The real risk shows up when you created your tables across mixed collation environments, some on older MySQL defaults (latin1/utf8mb4_general_ci from pre-8.0 instances), others on the current utf8mb4_0900_ai_ci default. In that mixed state, an upgrade can expose join-column collation mismatches that the previous optimizer tolerated or routed around differently, surfacing errors or unexpected plan changes that weren’t visible before.

sql_mode and character set defaults: MySQL 8.0 ships with sql_mode set to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,… by default. If your previous parameter group customized sql_mode or character_set_server and those values weren’t carried forward to the new parameter group, queries that touched implicit defaults can behave differently. Always document these values before upgrading and verify them after.

Buffer pool state (RDS for MySQL only): On Amazon RDS for MySQL, a version upgrade restarts the DB instance, and the restart clears the InnoDB buffer pool. The first hours after an upgrade can show elevated ReadIOPS and latency not because the query plan changed, but because the buffer pool is cold. RDS supports innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to warm the cache automatically on restart. Verify that you have enabled these parameters in your parameter group before upgrading. On Aurora MySQL, the buffer pool uses a survivable page cache that persists across normal restarts, but a major version upgrade still clears it (Aurora performs a clean shutdown and rebuilds the engine), so you can see the same cold-cache effect after a major version upgrade on Aurora MySQL.

Step 1: Update table statistics

An engine upgrade may change how InnoDB samples data pages to estimate index cardinality. If you have stale or inaccurate table statistics, the optimizer picks a suboptimal plan. This is the cheapest fix to try first.

ANALYZE TABLE schema_name.table_name;

After refreshing, re-run the slow query and compare. If it is still slow, verify that the stored estimates reflect reality:

-- Stored cardinality:
SHOW INDEX FROM schema_name.table_name;

-- Actual distinct values:
SELECT COUNT(DISTINCT column_name) FROM schema_name.table_name;

If the stored cardinality (from SHOW INDEX) and the actual distinct count (from COUNT(DISTINCT)) diverge by more than 30 percent, the sampling depth is likely insufficient. You have two options on RDS for MySQL and Aurora MySQL. The innodb_stats_persistent_sample_pages parameter is global-only and cannot be set at the session level.

Option A: Per-table override (no parameter group change)

ALTER TABLE schema_name.table_name STATS_SAMPLE_PAGES = 40;
ANALYZE TABLE schema_name.table_name;

-- Revert later:
ALTER TABLE schema_name.table_name STATS_SAMPLE_PAGES = DEFAULT;

Option B: Parameter group change (affects all tables)

aws rds modify-db-parameter-group \
    --db-parameter-group-name your-parameter-group-name \
    --parameters "ParameterName=innodb_stats_persistent_sample_pages,ParameterValue=40,ApplyMethod=immediate"

This is a dynamic parameter with no reboot required. If statistics look accurate and the query is still slow, the optimizer is making a deliberate cost-based choice. Move to Step 2.

Step 2: Examine the execution plan

The goal here is narrow: find what changed between your old plan and your new one. If you captured baselines before the upgrade, this is a comparison exercise. If you didn’t, you’re looking for the regression signature in the current plan.

EXPLAIN ANALYZE
SELECT ... -- the slow query
\G

EXPLAIN ANALYZE executes the query and returns actual row counts and timing alongside estimates. It is available in MySQL 8.0.18 and later. The \G modifier formats output vertically and is MySQL CLI-only. It is not available in the RDS Query Editor or most GUI clients.

For a full reference on reading EXPLAIN output, see the MySQL EXPLAIN documentation. What matters for upgrade regression diagnosis is the before-and-after delta.

Here’s an example of what a regression can look like:

Before the upgrade (targeted index lookup):

After the upgrade (plan regression):

This is the signature: an index that existed and worked before the upgrade is now being ignored. After you’ve confirmed this pattern, proceed to Step 3 to match it to the specific version change that caused it.

Step 3: Remediation by upgrade trigger

Each of the following sections leads with the specific MySQL version change that introduced the regression. Work through the sections that match what your EXPLAIN output shows.

3.1 Index selection changed: Cost model recalibrated across every 8.0.x release

Every 8.0.x release ships with a revised optimizer cost model. An index access path that your previous version considered cheapest may carry a higher estimated cost under the new model, even when your statistics are accurate and unchanged. This is a common cause of regressions that appear without any other obvious trigger.

If key in your post-upgrade EXPLAIN shows a different index than before, test the previous index directly:

EXPLAIN ANALYZE
SELECT * FROM schema_name.table_name FORCE INDEX (previous_index_name)
WHERE conditions ...
\G

If the forced index restores performance, the cost model change is the cause. You can apply an index hint as an interim fix:

SELECT * FROM table_name FORCE INDEX (index_name) WHERE ...;

The durable fix is a composite index. When a query both filters and sorts, put the WHERE (equality) columns first in the index, followed by the ORDER BY columns. The engine then uses the index to narrow rows and read them already in sorted order, which avoids a separate filesort step. That is why the ORDER BY columns belong in the index, not only the WHERE columns. With such an index, the optimizer arrives at the correct choice on its own, regardless of future cost model revisions. Index hints paper over the symptom. The composite index removes it. (Section 3.7 covers the ORDER BY / filesort case in detail.)

3.2 Hash join changed: Expanded in 8.0.18 and 8.0.20

MySQL 8.0.18 introduced hash join for equi-joins that have no usable index. MySQL 8.0.20 then removed the older block nested loop (BNL) algorithm, so hash join now replaces BNL in all cases where it was previously used. If your upgrade crossed either boundary, the optimizer may choose a hash join for a query that previously ran as a nested loop.

How to spot it in EXPLAIN ANALYZE: Look for a Hash node building from a full table scan with a large row count, or Using join buffer (Block Nested Loop) indicating no usable index for the join. The same join, before and after the upgrade:

Before the upgrade (nested loop with index lookup, fast):

-> Nested loop inner join (cost=812 rows=1)
    -> Index range scan on orders using idx_status (rows=1,240)
    -> Single-row index lookup on customers using PRIMARY
        (id=orders.customer_id) (rows=1 loops=1,240)

After the upgrade (hash join, regressed):

-> Inner hash join (customers.id = orders.customer_id) (cost=very_high rows=1,240)
    -> Table scan on orders (rows=1,240)
    -> Hash
        -> Table scan on customers (rows=2,500,000) <-- build side scans the ENTIRE table

The signature: The Hash node builds from a full table scan of the inner table (2,500,000 rows in customers), whereas the previous plan did a single-row primary-key lookup per matched order. The optimizer hashed the whole inner table instead of using its index, so a query that returns approximately 1,240 rows now reads millions.

-- Confirm by disabling hash join at the session level:
SET SESSION optimizer_switch = 'hash_join=off';
EXPLAIN ANALYZE SELECT ... \G

If nested loop restores performance, the root cause is a missing index on the join column of the inner table, not the hash join itself. Add the index and hash join becomes irrelevant:

-- Per-query workaround while you add the index:
SELECT /*+ NO_HASH_JOIN(t1, t2) */ ...

Replace t1 and t2 with your actual table names.

After the inner table join column is indexed, the optimizer will choose nested loop with index lookup for selective queries without any hints.

3.3 Collation mismatch: collation_server default changed on the major path

The server default collation changed at MySQL 8.0, not between 8.0 and 8.4. MySQL 5.7 defaulted to latin1 / latin1_swedish_ci (and utf8mb4’s pre-8.0 default was utf8mb4_general_ci), whereas MySQL 8.0 and 8.4 both default to utf8mb4 / utf8mb4_0900_ai_ci. A straight 8.0-to-8.4 upgrade therefore does not change the default, but a 5.7-to-8.4 upgrade crosses the 8.0 boundary where it did change. If your tables were created under different collation environments, the upgrade can expose join-column collation mismatches that prevent index use. The symptom in EXPLAIN is type=ALL on a join that previously showed type=ref, with Using join buffer in Extra. For the current server defaults, see Server Character Set and Collation.

-- Check collations on the join columns:
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND COLUMN_NAME IN ('column_from_table_a', 'column_from_table_b');

If the two columns have different COLLATION_NAME values, MySQL will not use an index for that join. You can force alignment at query time by adding a COLLATE clause in the join condition:

... ON a.column = b.column COLLATE utf8mb4_0900_ai_ci

The permanent fix is aligning the column collations at the schema level:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

This operation rewrites the table and rebuilds all indexes. It runs with ALGORITHM=COPY (a full table copy), not a lightweight metadata-only change. For large tables, schedule it during a maintenance window, and on Aurora MySQL, test on a clone first. During the rebuild, it blocks writes to the table while generally still allowing concurrent reads. MySQL also takes a brief exclusive metadata lock during the execution phase, and again in the final phase when it swaps the table definition. On a large table this is a lengthy, write-blocking operation, not a brief lock. Once collations align, the optimizer can use the index natively on future queries, including after the next upgrade.

3.4 Subquery materialization changed: subquery_to_derived enabled by default in 8.0.22+

MySQL 8.0.22 introduced the subquery_to_derived flag, and later 8.0.x releases enable it by default. If you upgraded across this boundary, a query using IN (SELECT …) or EXISTS (SELECT …) might now show a Materialize operation in EXPLAIN ANALYZE where the previous version used a more efficient semi-join strategy.

SET SESSION optimizer_switch = 'subquery_to_derived=off';
EXPLAIN ANALYZE SELECT ... \G

If disabling the flag restores the semi-join plan, apply it per-query using SELECT /+ SET_VAR(optimizer_switch=‘subquery_to_derived=off’) / … or set it at the parameter group level after validating against your full workload.

3.5 Data distribution not reflected in statistics: Use histograms

When you upgrade to MySQL 8.0 or later, the new engine does not carry histogram data forward from the previous version, so the optimizer loses the skewed-distribution signal it relied on and might choose a full scan where a selective index lookup previously applied. On Aurora MySQL 8.4.x, automatic histogram updates were also disabled, making a manual refresh mandatory after every upgrade.

This section applies when statistics are accurate (cardinality values match COUNT(DISTINCT)) but the optimizer still picks the wrong plan because the data is highly skewed, meaning one value accounts for the majority of rows. Standard cardinality statistics assume uniform distribution. Histograms tell the optimizer the actual shape of the data.

-- Check for skew:
SELECT column_name, COUNT(*) AS frequency
FROM schema_name.table_name
GROUP BY column_name
ORDER BY frequency DESC
LIMIT 10;

-- Create histogram:
ANALYZE TABLE schema_name.table_name UPDATE HISTOGRAM ON column_name WITH 100 BUCKETS;

Histograms are most effective for columns used in WHERE clauses with literal values. They are less effective with parameterized prepared statements.

On Aurora MySQL 8.4.7 and later, Aurora disables automatic histogram updates (AUTO_UPDATE) and treats them as MANUAL_UPDATE. This is intentional: Aurora 8.4 decouples histogram refreshes from routine ANALYZE TABLE to prevent unintended plan changes during maintenance. You need to refresh histograms manually after significant data changes. Consider adding ANALYZE TABLE … UPDATE HISTOGRAM to your maintenance runbook.

3.6 Derived table merge behavior changed: derived_merge

Starting in MySQL 8.0.16, the derived_merge optimizer switch was enabled by default, meaning subqueries in the FROM clause that were previously materialized into a temporary table are now merged directly into the outer query. If you are upgrading from a version before 8.0.16, subqueries that relied on materialization order may now be merged (or the reverse on a downgrade path). Look for Materialize in EXPLAIN ANALYZE where it wasn’t present before, or its unexpected disappearance.

SET SESSION optimizer_switch = 'derived_merge=off';
EXPLAIN ANALYZE SELECT ... \G
SET SESSION optimizer_switch = 'derived_merge=on';
EXPLAIN ANALYZE SELECT ... \G

Toggle the flag at the session level to identify which behavior your query prefers. If confirmed, set it at the parameter group level. For per-query control: SELECT /+ SET_VAR(optimizer_switch=‘derived_merge=off’) / …

3.7 Filesort introduced: prefer_ordering_index became default in 8.0.27

If Using filesort appears in Extra after an upgrade from 8.0.26 or earlier, this is almost certainly prefer_ordering_index. Introduced in 8.0.27 and defaulting to on, this flag causes the optimizer to prefer an index that avoids a sort operation over one that filters more efficiently, even when filtering first would examine far fewer rows.

Aurora MySQL 3.04 and later (mapping to community MySQL 8.0.28 and later) includes this flag. If you’re on Aurora MySQL 3.03 or earlier, this flag doesn’t exist in your version and this section doesn’t apply.

The signature in EXPLAIN: key points to the ORDER BY column’s index, rows is very high, and there is no filesort, but performance is poor because the optimizer is scanning a large index range to avoid sorting.

SET SESSION optimizer_switch = 'prefer_ordering_index=off';
EXPLAIN SELECT ... \G

If disabling it restores the expected plan, set it at the parameter group level, or use a per-query hint: SELECT /+ SET_VAR(optimizer_switch=‘prefer_ordering_index=off’) / …

The permanent fix is a composite index covering both the WHERE and ORDER BY columns:

ALTER TABLE table_name ADD INDEX idx_status_created (status, created_at);

This gives the optimizer a single index that satisfies both filtering and sorting, and keeps working correctly regardless of the prefer_ordering_index setting in the future.

3.8 IN-list estimation changed: eq_range_index_dive_limit not carried forward

The eq_range_index_dive_limit parameter controls the cutoff between index dives (precise row count estimation) and index statistics (cardinality-based estimation) for IN-list queries. Its default is 200. If your previous parameter group had a custom value (for example, 10) and that value wasn’t carried forward to your new parameter group, queries with IN-lists of 10–199 values now use a different estimation method, which can produce different plan choices.

-- Check what's currently set:
SHOW VARIABLES LIKE 'eq_range_index_dive_limit';

-- Test with the previous value:
SET SESSION eq_range_index_dive_limit = 10;
EXPLAIN ANALYZE SELECT ... WHERE column IN (value1, ..., valueN) \G

eq_range_index_dive_limit is a dynamic parameter with no reboot required to change it in the parameter group. Lower values favor index statistics. Higher values favor index dives (more precise but slower to compute for large IN-lists). The fix here is ensuring your custom value is present in the new parameter group. Check your previous group’s configuration and verify it carried over.

Buffer pool warm-up after restart

An engine upgrade requires a database restart. MySQL provides two parameters to avoid a cold-start performance valley: innodb_buffer_pool_dump_at_shutdown serializes the hot page list at shutdown, and innodb_buffer_pool_load_at_startup allows the instance to resume from a warm cache instead of reading pages from storage one by one. These parameters remain valid in MySQL 8.0 and 8.4.

The InnoDB buffer pool dump (ib_buffer_pool) stores only tablespace IDs and page IDs, not page contents, so it is small and not tied to a specific engine binary format. On reload, innodb_buffer_pool_load_at_startup re-reads those pages from the current data files. On a major version upgrade, however, treat pre-warming as best-effort: the upgrade restarts the instance and may reorganize internal structures, and RDS manages the dump/load lifecycle itself. Validate the outcome rather than assuming it. After the upgrade, confirm in the MySQL error log that the buffer pool load completed before relying on a warm cache. For details, see Saving and restoring the buffer pool state.

Enable both parameters in your DB parameter group before the upgrade window. They are dynamic on RDS for MySQL (no reboot required to set them, but the dump/load cycle happens at shutdown and startup):

aws rds modify-db-parameter-group \
    --db-parameter-group-name <your-param-group> \
    --parameters "ParameterName=innodb_buffer_pool_dump_at_shutdown,ParameterValue=1,ApplyMethod=immediate" \
    --parameters "ParameterName=innodb_buffer_pool_load_at_startup,ParameterValue=1,ApplyMethod=immediate"

You can monitor warm-up progress after a restart:

SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

Aurora MySQL uses a survivable page cache each instance’s buffer pool is managed in a separate process from the database, so it survives normal engine restarts. A major version upgrade is different: Aurora performs a clean shutdown and upgrades the engine, and the buffer cache is cleared during the upgrade. So after a major version upgrade, Aurora MySQL starts with a cold cache just like RDS for MySQL. Allow 15–30 minutes of normal traffic before concluding that a regression is real rather than a cold-cache artifact.

Detecting regressions with Amazon RDS CloudWatch Database Insights

Rather than waiting for a customer report, you can use Database Insights to catch plan regressions as they develop. After an upgrade, open the Top SQL view in Database Insights and sort by Average Active Sessions (AAS). A query that wasn’t previously in your top consumers but appears after the upgrade is a regression candidate.

The key signal is a query that appears in Top SQL dominated by io/table/sql/handler. This wait represents table access through the storage-engine handler API. It occurs whether or not the page is cached and rises with the number of rows accessed. A sharp increase means the query is reading far more rows per execution than before (a classic plan regression, such as an index lookup turning into a full table scan). By contrast, wait/io/file/sql/query_log is a file-I/O instrument for the general query log. It measures logging overhead, not row reads, so don’t mistake it for a plan regression. Cross-reference with these Amazon CloudWatch metrics to confirm:

Set long_query_time to 1 second for the first 24–48 hours after an upgrade. This surfaces regressions that are real but not yet severe enough to trigger your normal alerting threshold.

Proactive measures for future upgrades

The most effective time to address post-upgrade regressions is before the upgrade.

• Capture EXPLAIN baselines. Before upgrading, run EXPLAIN on your top 10 most frequently executed queries and save the output. This turns post-upgrade investigation from guesswork into a direct comparison.
• Document optimizer configuration. Record optimizer_switch, sql_mode, character_set_server, and collation_server from the current parameter group. Verify each value is present in the new parameter group after the upgrade.
• Use Blue/Green Deployments. With a Blue/Green Deployment, you can create a Green environment on the target version, validate query plans, and run EXPLAIN comparisons before cutting over. Don’t send live production write traffic to Green: Green replicates from Blue, and writes issued directly on Green can cause replication conflicts or data drift that make the subsequent switchover unsafe. For read-plan validation, run EXPLAIN/EXPLAIN ANALYZE and read-only queries on Green, or replay a captured read workload. Keep all production writes on Blue until switchover.
• Run ANALYZE TABLE immediately after the upgrade. Don’t wait for the automatic statistics refresh cycle. Refreshing statistics on critical tables right after the upgrade gives the optimizer accurate cardinality from the start.
• Verify buffer pool warm-up (RDS for MySQL). Confirm innodb_buffer_pool_dump_at_shutdown=1 and innodb_buffer_pool_load_at_startup=1 are set in your parameter group. After the upgrade restart, check the error log for InnoDB: Buffer pool(s) load completed to confirm the cache was restored. Aurora MySQL users can skip this step. The Aurora buffer pool survives restarts.

Conclusion

Post-upgrade regressions are diagnosable. Start with ANALYZE TABLE, then use EXPLAIN ANALYZE to find what changed in the plan. Each section in Step 3 maps directly to a specific version change: hash join expansion, prefer_ordering_index becoming a default, collation shifts, parameter group values not carried forward. Work through the sections that match your EXPLAIN findings.

As your next step: run EXPLAIN on your top 10 queries now and save the output. When your next upgrade happens, you will have the baseline you need to diagnose in minutes rather than hours.

About the authors