Understanding the InnoDB Adaptive Hash Index (AHI)
The InnoDB Adaptive Hash Index (AHI) is an in-memory hash table that InnoDB automatically builds from frequently accessed B-tree index entries, allowing O(1) hash lookups to replace O(log n) B-tree traversals for hot key lookups. When effective, the AHI can reduce read latency by 2–10× for point queries on heavily accessed indexes. The HASH INDEX section of SHOW ENGINE INNODB STATUS reports AHI search rates, memory consumption, and cell utilisation. However, the AHI is also a source of internal latch contention under high write concurrency, and MySQL 8.0 introduced AHI partitioning to mitigate this. Understanding when the AHI helps versus hurts your workload — and how to read the statistics to make that determination — is the focus of this guide.
How the Adaptive Hash Index Works
AHI Construction: From B-Tree to Hash Table Lookups
InnoDB constructs AHI entries by observing B-tree access patterns. When the same B-tree page is accessed using the same search prefix more than a configurable number of times, InnoDB promotes the page's key entries into the hash table. The AHI entries map primary key values (or secondary index key + primary key combinations) directly to physical page locations, allowing InnoDB to skip the B-tree traversal entirely for subsequent identical lookups. AHI construction is transparent and automatic; you cannot directly populate or invalidate entries. When a B-tree page is split, merged, or evicted from the buffer pool, all corresponding AHI entries are immediately invalidated and must be rebuilt from scratch — which is why write-heavy workloads that cause frequent B-tree restructuring see less AHI benefit.
Pattern Detection: Access Frequency Before AHI Entry
InnoDB uses a pattern detection algorithm to decide when to build an AHI entry for a given index key prefix. Each B-tree cursor access to a page increments a per-page access counter; when the counter exceeds a threshold proportional to the number of entries per page, InnoDB decides the page is "hot enough" to merit AHI coverage. Only full-prefix equality predicates (WHERE pk = N or WHERE uk = N) benefit from AHI lookups; range queries, LIKE patterns, and partial index prefix scans still traverse the B-tree. The minimum access frequency threshold is not directly configurable — it is determined internally by InnoDB based on observed access patterns. If your workload consists primarily of range scans or JOIN conditions that use only partial key prefixes, the AHI provides minimal benefit.
Reading AHI Statistics in InnoDB Status
Hash Searches/s vs Non-Hash Searches/s: The Decision Ratio
The SHOW ENGINE INNODB STATUS line "Hash table size N, node heap has M buffer(s), K.K hash searches/s, L.L non-hash searches/s" is the primary diagnostic for AHI effectiveness. The ratio hash_searches / (hash_searches + non_hash_searches) shows what fraction of lookups are served from the AHI. A ratio above 80% indicates excellent AHI utilisation — the hash index is accelerating the majority of lookups. A ratio below 20% means most lookups still traverse the B-tree, and the AHI is consuming buffer pool memory and maintenance overhead without providing significant performance benefit. In that case, disabling the AHI with innodb_adaptive_hash_index=OFF and observing query latency is the recommended diagnostic step.
AHI Memory Consumption from the Buffer Pool
The AHI is allocated from the InnoDB buffer pool, not from a separate memory pool. The "Hash table size N" value in the AHI statistics shows the number of hash cells, each consuming approximately 8 bytes of buffer pool memory. With a 10 million cell hash table, the AHI occupies roughly 80 MB of buffer pool space — space that could otherwise hold 5,000 additional 16 KB data pages. On buffer-pool-constrained servers (hit rate < 90%), the AHI's buffer pool consumption may reduce overall hit rate more than the hash lookup speedup compensates. Use SELECT COUNT FROM information_schema.INNODB_BUFFER_PAGE WHERE PAGE_TYPE='IBUF_INDEX' to verify AHI page consumption before deciding whether to disable it.
When to Disable the Adaptive Hash Index
High-Concurrency Workloads and AHI Latch Contention
Under high write concurrency, the AHI can become a latch contention bottleneck rather than a performance accelerator. Every AHI modification (entry insertion, invalidation on page split) acquires an exclusive AHI latch, blocking all concurrent AHI readers. On servers running 256+ simultaneous connections with a mix of reads and writes, AHI latch waits can appear in the SEMAPHORES section as "btr0sea.cc" mutex waits. The diagnostic signal is a high ratio of "non-hash searches/s" accompanied by elevated semaphore wait counts for AHI latches. In such cases, disabling innodb_adaptive_hash_index=OFF typically reduces overall latency by 10–30% despite losing the O(1) lookup benefit.
Partitioned AHI in MySQL 8.0 to Reduce Contention
MySQL 8.0 introduced AHI partitioning (innodb_adaptive_hash_index_parts, default 8) to reduce latch contention by dividing the AHI into multiple independent hash tables, each with its own latch. With 8 partitions, the probability of two concurrent threads contending for the same AHI partition latch is reduced to approximately 1/8th of the single-partition probability. For workloads that previously exhibited AHI latch contention in MySQL 5.7, upgrading to MySQL 8.0 with the default 8 partitions typically eliminates visible AHI latch waits without requiring AHI to be disabled. If contention persists, increase innodb_adaptive_hash_index_parts to 16 or 32 — the memory overhead per additional partition is minimal relative to the latch contention reduction benefit.