The Adaptive Hash Index (AHI) is an automatic in-memory optimization that creates hash indexes for frequently accessed data. It provides direct pointer access to data pages, making equality lookups much faster than traditional B-tree traversals.
Traditional B-tree Search:
Root Node → Intermediate Node → Leaf Node → Data Page
(3-4 steps)
With AHI:
Hash Key → Direct Data Page Pointer
(1 step)
Practical Examples
-- Check if AHI is enabled
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
-- Create sample table
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) NOT NULL,
INDEX idx_email (email)
);
-- Monitor AHI activity
SHOW ENGINE INNODB STATUS\G
-- Look for "INSERT BUFFER AND ADAPTIVE HASH INDEX" section
When AHI Shines
-- Repeated queries that benefit from AHI
SELECT * FROM users WHERE email = 'john@example.com';
SELECT * FROM users WHERE id = 1000;
-- After multiple executions, AHI builds hash entries
-- making subsequent lookups lightning fast
Real-World Scenarios
-- Frequent product searches by SKU
SELECT * FROM products WHERE sku = 'PROD-12345-XL';
-- Session management
SELECT * FROM user_sessions WHERE session_id = 'sess_abc123';
Monitoring AHI Effectiveness
-- Calculate AHI hit ratio (MySQL 8.0+)
SELECT
'AHI Hit Ratio' AS metric,
ROUND(
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_searches') /
(
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_searches') +
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_searches_btree')
) * 100, 2
) AS value_percentage;
Configuration & Best Practices
-- Toggle AHI
SET GLOBAL innodb_adaptive_hash_index = ON; -- or OFF
-- In my.cnf for permanent setting
[mysqld]
innodb_adaptive_hash_index = ON
innodb_adaptive_hash_index_parts = 8 -- Reduce contention
Quick Decision Guide
Your Workload AHI Recommendation
Read-heavy, repeated lookups ✅ Enable
Write-heavy, frequent updates ❌ Disable
Mixed workload 🔄 Test both ways
Memory limited ❌ Disable
Typical Results:
With AHI: Queries can be 2-5x faster for repeated lookups
Memory Usage: AHI consumes additional RAM for hash tables
Maintenance Overhead: Updates require rebuilding hash entries
The Change in MySQL 8.4
In previous versions of MySQL, the innodb_adaptive_hash_index variable was set to ON by default. However, this was changed in the 8.4 LTS release. The following table compares the default values:
MySQL Version Default AHI Setting
MySQL 8.4 LTS OFF
Pre-8.4 Versions ON
The change was made because the performance benefits of AHI are highly dependent on a specific type of workload. In many common scenarios, especially those with write operations or high concurrency, maintaining the AHI can become a source of contention and hurt performance. Disabling it by default leads to more predictable performance for most users