The Adaptive Hash Index: Understanding and Tuning Automatic InnoDB Optimization

1. What the Adaptive Hash Index actually does

InnoDB organizes its indexes as B-trees by default, where an equality lookup has to traverse several pages from root to leaf. The Adaptive Hash Index, or AHI, continuously watches which search patterns repeat on which pages inside the buffer pool, and for pages that get queried the same way often enough, it additionally builds a hash structure directly in memory that maps the search key straight to the record pointer.

Once a hash structure has been built for a given access pattern, InnoDB can skip the B-tree entirely for that type of request and find the record directly through the hash. The AHI is fully automatic: there is no manual selection of which indexes get hashed, InnoDB decides purely based on observed access frequency against an internal threshold.

2. How InnoDB decides when a hash structure gets built

InnoDB tracks, per page, how often it gets queried with the same prefix of search columns. Only once a page has been searched consistently with the same column prefix across a certain number of consecutive accesses does InnoDB create hash entries for that page. Pure range queries or heavily varying search patterns rarely or never reach this threshold, which is why the AHI effectively only matters for equality lookups on pages that are hot for a single, specific pattern.

Since MySQL 5.7 the AHI is internally partitioned, into eight partitions by default via innodb_adaptive_hash_index_parts. Before this partitioning, a single global latch protected the entire hash structure, which itself became a bottleneck under high concurrency. Splitting it across several independent partitions reduces that contention noticeably, though it does not eliminate it completely.

-- Check current partitioning and enable status
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index%';

-- Inspect hash activity in the InnoDB status report
SHOW ENGINE INNODB STATUS\G
-- look for the "INSERT BUFFER AND ADAPTIVE HASH INDEX" section

3. When the AHI actually delivers a measurable benefit

The classic ideal case for the AHI is a workload with many repeated point lookups on the same, well-cached pages, for example primary key lookups or unique secondary index lookups in an OLTP application with a manageable working set. Here the hash structure saves several levels of B-tree traversal per request, and the saving adds up noticeably at very high request frequency.

In a Magento context, this most likely applies to lookups on small, well-cached reference tables with high repetition, for example repeated store config or EAV attribute metadata access during page rendering. For large, constantly changing tables with diverse access patterns, such as the product catalog during a layered navigation search, the effect is considerably smaller.

4. Scenarios where the AHI turns counterproductive

The AHI becomes counterproductive mainly when the working set is larger than what can be usefully hashed, or when access patterns keep changing, for example under diverse reporting and search queries. In that case InnoDB keeps building new hash entries and discarding old ones, producing pure administrative overhead without meaningful benefit.

The situation gets more serious under high concurrent write load: every change to a row that is part of a hash structure also has to update the corresponding hash entry, which requires extra latch access on the affected AHI partition. Under heavily parallel insert or update workloads, for example during a bulk import or a reindex, this extra synchronization can measurably lower throughput instead of raising it.

5. Measuring the AHI's effect: hash searches vs. B-tree fallback

The InnoDB status report's Adaptive Hash Index section shows two key figures: the number of hash searches per second, and the number of searches that fell back to the regular B-tree instead. A high share of hash searches with a low B-tree fallback rate points to a workload that genuinely benefits from the AHI.

On top of that, information_schema.INNODB_METRICS exposes the adaptive_hash_searches and adaptive_hash_searches_btree counters as cumulative, queryable values that can be tracked over time and wired into a monitoring dashboard, instead of parsing the full status text every time.

SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME IN ('adaptive_hash_searches', 'adaptive_hash_searches_btree');

6. Deliberately disabling the AHI and testing the effect in a controlled way

innodb_adaptive_hash_index can be toggled at runtime via SET GLOBAL, with no restart required. That allows a direct A/B comparison under realistic load: disable the AHI for a defined window, measure throughput and latency, re-enable it, and repeat the same measurement.

It matters not to run this test on an empty or freshly started system, since the buffer pool and, with it, the hash structure need to warm up first. A reliable comparison requires both measurement windows to happen under comparable, steady-state load, otherwise the warm-up effect skews the result.

-- Disable AHI at runtime, no restart needed
SET GLOBAL innodb_adaptive_hash_index = OFF;

-- Re-enable after the measurement window
SET GLOBAL innodb_adaptive_hash_index = ON;

7. Persisting configuration and adjusting partitioning

If a test shows the AHI permanently hurts more than it helps for a given workload, the setting should also be persisted in the configuration file so it survives a restart. Conversely, for a workload that clearly benefits from the AHI but suffers from latch contention, a higher partition count via innodb_adaptive_hash_index_parts can help, though it is only configurable at server startup, not at runtime.

In practice, this fine-tuning only pays off for systems with measurably very high contention on AHI partitions. For most Magento stores, the default of eight partitions is entirely sufficient, and the more important decision remains whether the AHI should stay enabled at all for the specific workload in question.

8. Invalidation on buffer pool eviction and schema changes

The AHI is tightly coupled to the lifecycle of its underlying buffer pool page. Once a page gets evicted from the buffer pool because the available memory is needed for other, currently more active data, InnoDB automatically discards the corresponding hash entries as well. A hash entry therefore never exists independently of its source page, only for as long as that page is actually kept in memory, which makes the AHI implicitly self-regulating.

Structural changes have a direct effect too: if an index gets modified, rebuilt, or a table gets rewritten via ALTER TABLE, InnoDB discards the affected hash structures entirely and only rebuilds them through new, repeated access afterward. After a larger schema deployment that changes many indexes at once, the AHI is therefore effectively ineffective for a while, until stable access patterns settle back in.

9. Practical recommendation for Magento databases

For a typical Magento store with a mixed load of storefront reads, occasional order writes and periodic indexer runs, a deliberate test is worth more than a blanket recommendation. During normal storefront load, the AHI can genuinely help measurably, while during a full reindex run with high, parallel write load, the extra synchronization overhead outweighs the benefit in many measured cases.

A practical approach is to deliberately disable the AHI during scheduled, especially write-heavy maintenance windows and leave it enabled for normal storefront operation, provided the measurement actually shows a benefit. Without an actual measurement on the specific system, any blanket recommendation to switch the AHI on or off remains pure speculation.

10. Summary

11. FAQ: Adaptive Hash Index: The Essentials at a Glance